INDIRECT in Data Validation

[UntitledDocument]

I've got three tables

TableA:

List of Tables	Other Info
TableB	Info1
TableC	Info2

<tbody>
</tbody>

TableB:

Column A	Column B
stuff	morestuff
otherstuff	extrastuff

<tbody>
</tbody>

TableC:

Column A	Column B
onestuff	redstuff
twostuffs	bluestuff

<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:

	A	B	C	D	E
1
2		DropDown1=	TableB
3		DropDown2=	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.

 

[Yongle]

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 Tables	Other Info
2​	TableB	Info1
3​	TableC	Info2
4​
5​
6​	Column A	Column B
7​	stuff	morestuff
8​	otherstuff	extrastuff
9​
10​
11​	Column A	Column B
12​	onestuff	redstuff
13​	twostuffs	bluestuff
14​
15​
16​	TableB
17​	stuff
18​

Sheet: Sheet4

 

[Yongle]

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_

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

 

[UntitledDocument]

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]

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.