INDIRECT in Data Validation

UntitledDocument

New Member
Joined
Apr 22, 2019
Messages
9
I've got three tables

TableA:
List of TablesOther Info
TableBInfo1
TableCInfo2

<tbody>
</tbody>

TableB:
Column AColumn B
stuffmorestuff
otherstuffextrastuff

<tbody>
</tbody>

TableC:
Column AColumn B
onestuffredstuff
twostuffsbluestuff

<tbody>
</tbody>

I ultimately required info from say TableB, ColumnA. I want to get this info via two data validation drop down lists.

the first drop down lets me choose different tables listed in TableA using =INDIRECT("TableA
[List of Tables]"). this I have achieved.

with the second drop down I want to choose an item from Column A in the table previously selected from the first dropdown. This is the one I need help on.

So if my two drop downs selects are shown on a sheet:

ABCDE
1
2DropDown1=TableB
3DropDown2=otherstuff
4
5

<tbody>
</tbody>

I first chose TableB from my two options (TableB,TableC) from TableA
[List of Tables]
I then chose otherstuff from my two options (stuff, otherstuff) from TableB[Column A]

How would I write the indirect formula for the second drop down? My guess was something like =INDIRECT(C2[Column A])

I'm not really looking for another route around this because in reality my "TableA" is an ever-changing list of table names with an ever-changing amount of information in each table.
Just a heads up in case you think I'm looking for a complicated solution to a simple problem.

Thank you.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,634
Office Version
365
Platform
Windows
Create 3 Named Ranges
-whose names are names of the original tables followed by underscore
-with RefersTo being the structured reference to the first column in each table as illustrated below

TableA_
RefersTo: =TableA
[List of Tables]

TableB_
RefersTo: =TableB[Column A]

TableC_
RefersTo: =TableC[Column A]

Add data validation

In A16
Allow: List
Source: =TableA_

In A17
Allow: List
Source: =INDIRECT(A16&"_")


It works for me:
Excel 2016 (Windows) 32 bit
A
B
1
List of TablesOther Info
2
TableBInfo1
3
TableCInfo2
4
5
6
Column AColumn B
7
stuffmorestuff
8
otherstuffextrastuff
9
10
11
Column AColumn B
12
onestuffredstuff
13
twostuffsbluestuff
14
15
16
TableB
17
stuff
18
Sheet: Sheet4
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,634
Office Version
365
Platform
Windows
:confused::confused:
I have no idea why
[List of Tables]
ended up on the next line down
- I tried to correct it, but for some reason it keeps ending up on the next line down when I update the post
- obviously it is part of the RefersTo formula for named range TableA_


:confused:


edit .. forum software has also done it on this post!
 
Last edited:

UntitledDocument

New Member
Joined
Apr 22, 2019
Messages
9
Thank Yongle. That solution makes a lot of sense to me. I'll try it out.

I was confused and then irritated when
[List of Tables] started moving around on me too.
 

UntitledDocument

New Member
Joined
Apr 22, 2019
Messages
9
I realized soon after reading this that I almost had it right.

I guessed =INDIRECT(C2[Column A])
The proper way to write it is
=INDIRECT(C2 & "[Column A]")

I still owe you thanks for showing me the proper syntax, and showing a method I can use if I choose to have different column names in each table.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,350
Messages
5,443,953
Members
405,258
Latest member
daveyf

This Week's Hot Topics

Top