INDIRECT in Data Validation

[UntitledDocument]

I've got three tables

TableA:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]List of Tables[/TD]
[TD]Other Info[/TD]
[/TR]
[TR]
[TD]TableB[/TD]
[TD]Info1[/TD]
[/TR]
[TR]
[TD]TableC[/TD]
[TD]Info2[/TD]
[/TR]
</tbody>[/TABLE]

TableB:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]stuff[/TD]
[TD]morestuff[/TD]
[/TR]
[TR]
[TD]otherstuff[/TD]
[TD]extrastuff[/TD]
[/TR]
</tbody>[/TABLE]

TableC:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]onestuff[/TD]
[TD]redstuff[/TD]
[/TR]
[TR]
[TD]twostuffs[/TD]
[TD]bluestuff[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]DropDown1=[/TD]
[TD]TableB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]DropDown2=[/TD]
[TD]otherstuff[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]List of Tables[/td][td=bgcolor:#4472C4]Other Info[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]TableB[/td][td=bgcolor:#D9E1F2]Info1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]TableC[/td][td]Info2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#4472C4]Column A[/td][td=bgcolor:#4472C4]Column B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#D9E1F2]stuff[/td][td=bgcolor:#D9E1F2]morestuff[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]otherstuff[/td][td]extrastuff[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td=bgcolor:#4472C4]Column A[/td][td=bgcolor:#4472C4]Column B[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td=bgcolor:#D9E1F2]onestuff[/td][td=bgcolor:#D9E1F2]redstuff[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]twostuffs[/td][td]bluestuff[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td=bgcolor:#C6E0B4]TableB[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td=bgcolor:#FFF2CC]stuff[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[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.