Use tables as data validation drop down list items and return table

Barque

New Member
Joined
Nov 25, 2020
Messages
15
Office Version
  1. 365
I cannot seem to find an answer to this on the internet or the forums.

I have numerous tables. I would like to select a specific table from a list of tables, and have that table returned in a cell next to the data validation drop down list.

For example, cell B4 contains a drop down list with the names contained in A4:A7. These names are tables as indicated by the name manager picture. I am hoping to selection a table name, and have that entire table be returned.

Is this possible without VBA? I will not be able to remember the name of all of my tables and entering =tablename into the cell is not cumbersome, but I am hoping to create a drop down list.

Many thanks.

Tables example2.JPG
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Assuming you have created excel tables for Single, Marriedf etc, the tables are named Single & Married, but I've created named ranges for each entire table.

When you choose the named range e.g. singletbl , that whole table spills dynamically

Use tables as data validation drop down list items and return table_Barque.xlsx
ABCDEFGHIJKL
1
2
3DataValidate
4singletblLOWER LIMITUPPER LIMITMARGINALFIXEDsingle
5AAAA
6AAAALOWER LIMITUPPER LIMITMARGINALFIXED
7AAAAAAAA
8AAAAAAAA
9AAAAAAAA
10AAAAAAAA
11AAAAAAAA
12AAAAAAAA
13AAAA
14AAAA
15Named Ranges
16singletblmarried
17marriedtbl
18LOWER LIMITUPPER LIMITMARGINALFIXED
19BBBB
20BBBB
21BBBB
22BBBB
23BBBB
24BBBB
25BBBB
26BBBB
Sheet1
Cell Formulas
RangeFormula
D4:G12D4=INDIRECT(B4)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B4List=$D$16:$D$17
 
Upvote 0
I have assumed the tables are 'formal' Excel tables (ListObjects). Since you have the same headings for all the tables there is no need to create the named ranges for the full tables.
Manually put the headers where you want the results (D3:F3 for my simple example) then the INDIRECT formula in the cell below the left header. I have added a little to keep it tidy in case nothing is entered in the DV cell.

Barque.xlsm
ABCDEFGHIJK
1
2
3Hdr1Hdr2Hdr3Hdr1Hdr2Hdr3
4Table1Table2101418147
5Table2111419258
6121620369
7131721
8
9Hdr1Hdr2Hdr3
10101418
11111419
12121620
13131721
14
Sheet1
Cell Formulas
RangeFormula
D4:F7D4=IF(B4="","",INDIRECT(B4))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B4List=$A$4:$A$5
 
Upvote 0
:biggrin: BTW, If the headers were different, we could do it like this.

Barque.xlsm
ABCDEFGHIJK
1
2
3abcHdr1Hdr2Hdr3
4Table1Table2101418147
5Table2111419258
6121620369
7131721
8
9abc
10101418
11111419
12121620
13131721
14
Sheet1 (2)
Cell Formulas
RangeFormula
D3:F3D3=IF(B4="","",OFFSET(INDIRECT(B4),-1,,1))
D4:F7D4=IF(B4="","",INDIRECT(B4))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B4List=$A$4:$A$5
 
Upvote 0
Another option, if you don't have too many tables
+Fluff 1.xlsm
ABCDEFGHIJK
1
2TblOne
3Hdr1Hdr2Hdr3Hdr1Hdr2Hdr3
4OneTwo101418147
5Two111419258
6121620369
7131721
8TblTwo
9Hdr1Hdr2Hdr3
10101418
11111419
12121620
13131721
14
Main
Cell Formulas
RangeFormula
D4:F7D4=SWITCH(B4,"One", TblOne,"two",TblTwo)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B4List=$A$4:$A$5
 
Upvote 0
Thank you all for the help! I am still learning the times when its best to use Named Ranges vs Tables. I was unaware I can use a table as a named range, thus creating a dynamic table that can be used in structured references, data validation, conditional formatting, etc.

The INDIRECT solution is one I've been trying to figure out, so thank you for the help. And the "how do I take my column names with the associated table" was going to be my next question.

(y)

I have just learned of the existence of SWITCH. Thank you.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
You're welcome.

And the "how do I take my column names with the associated table" was going to be my next question.
You could actually do headers and table all with a single formula like this.

Barque_1.xlsm
ABCDEFGHIJK
1
2
3abcHdr1Hdr2Hdr3
4Table1Table2101418147
5Table2111419258
6121620369
7131721
8
9abc
10101418
11111419
12121620
13131721
14
Sheet1 (2)
Cell Formulas
RangeFormula
D3:F7D3=IF(B4="","",LET(t,INDIRECT(B4),OFFSET(t,-1,,ROWS(t)+1)))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B4List=$A$4:$A$5
 
Upvote 0
Solution

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top