Data Validation

[gberg]

I'm trying to get a data validation list that will list 5 options based on the Item Value

The following example has "Item 1" and "Item 2" and the validation options for these items

I want to set data validation for cells B7 and B8 so that the options are per the list above

	A	B	C	D	E	F
1	Item	Option 1	Option 2	Option 3	Option 4	Option 5
2	Item 1	aaa	bbb	ccc	ddd	eee
3	Item 2	111​	222​	333​	444​	555​
4
5
6	Item	Option
7	Item 1
8	Item 2

Thanks

Greg

 

[awoohaw]

Greg, will youi only be putting data in row 7 and 8?
Or are you going to pull the formula/validaiton down more rows and input Item 1 or Item 2 at randome in column A of those rows?

 

[gberg]

Both the items with options and the list where I want the data validations will have a lot more rows

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F
1	Item	Option 1	Option 2	Option 3	Option 4	Option 5
2	Item 1	aaa	bbb	ccc	ddd	eee
3	Item 2	111	222	333	444	555
4
5
6	Item	Option
7	Item 1			=SWITCH(A7,$A$2,$B$2:$F$2,$A$3,$B$2:$F$3)
8	Item 2
9
10
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
B7	List	=SWITCH(A7,$A$2,$B$2:$F$2,$A$3,$B$3:$F$3)

 

[awoohaw]

I just edited the formulas.

 

[gberg]

How would you write that if these were tables Table1 would be the list with the options and Table2 would be where the validation goes?

 

[awoohaw]

How would you write that if these were tables Table1 would be the list with the options and Table2 would be where the validation goes?wEL

What happened when you tried to put it in the columns of the table?

 

[gberg]

I guess it works! I was trying to get the table references in but your formula works just as is! Thanks for your help

 

[awoohaw]

i'm not very good with referencing individual cells in tables. i'm glad that works for you. I'm not entirely sure it will work consistently though.
Best Wishes!

 

[gberg]

Sorry, this does not work. I will have many more items to reference and this only handles two