Is it possible to create a dropdown list Based on named tables?

[Dugzilla]

I have 3 tables: Table_1, Table_2, Week_3

All 3 tables are of the same structure and format.

The only difference being the data contained in the tables.

Is it possible to create a dropdown list that would give me a list of the 3 tables, THEN populate a worksheet with the appropriate data located in the table selected?

For example: Week_1 has the same structure as Week_2 and Week_3. If I select Week_1, I would only see the data from that particular table.

Please let me know if you require additional information.

Thank you so much.

 

[Eric W]

You could do something like this:

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Col1	Col2	Col3		Col1	Col2	Col3		Col1	Col2	Col3
2	a	1	2		u	1	2		m	1	2
3	b	2	3		v	2	4		n	2	7
4	c	3	5		w	3	8		o	3	1
5	d	4	7		x	4	16		p	4	2
6	e	5	11		y	5	32		q	5	2
7					z	6	64		r	6	8
8									s	7	1
9
10	# of tables	Names		DropDown		Displayed Table
11	3	Week_1		Week_2		u	1	2
12		Week_2				v	2	4
13		Week_3				w	3	8
14						x	4	16
15						y	5	32
16						z	6	64
17
Sheet2

Cell Formulas
Range	Formula
B11:B13	B11	="Week_"&SEQUENCE(A11)
F11:H16	F11	=INDIRECT(D11)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F11:H20	Expression	=F11<>""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D11	List	=B11#

I created 3 tables named Week_1, Week_2, and Week_3. I then put the number of tables in A11, and created a function to get the names in B11. Then I created a Data Validation in D11 using B11 as the list. Then you can choose the table name in D11, and the INDIRECT formula in F11 will return the table you want. I used Conditional Formatting around the F11:H20 range to show the outline if the cells aren't empty. This is because the INDIRECT only shows the data, not the formatting. So I used the CF to get a table grid, but that's up to you. If you really want, you can use CF to get the alternating colored bars too. Hope this helps!

 

[kevin9999]

Another option. If your tables are single column, name your tables the same as the table column header, and make those headers the choices for the initial dropdown. Like this:

Book1
	A	B	C	D	E	F	G
1	Week_1		Week_2		Week_3		Week_3
2	wk1 option 1		wk2 option 1		wk3 option 1		wk3 option 1
3	wk1 option 2		wk2 option 2		wk3 option 2
4	wk1 option 3		wk2 option 3		wk3 option 3
5
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
G1	List	Week_1,Week_2,Week_3
G2	List	=INDIRECT($G$1)

 

[Dugzilla]

Thank you for the reply. I will work on this today and let you know!

 

[Dugzilla]

Kevin 9999's solution worked best for me. That is so cool and will save me so much time. Thank you very much for your help.

 

[kevin9999]

Glad we could help & thanks for the feedback

 

[Dugzilla]

I'm sorry Kevin9999, but one more question. Not needing information from all columns, when I do an INDEX MATCH of the table it returns erroneous data. Is that due to the INDIRECT function?

VLOOKUP works.

 

[kevin9999]

I'd really need to see the full formula you're using, as well as your actual sheet. Are you able to post your sheet using the XL2BB add in?

 

[Dugzilla]

Got it figured out Kevin... thank you again.