Dynamic Data Validation

[TheRogue!]

I have a table for warehouse evaluation where the combination of the Section/Row/bay dictates the number of levels (high) the location is. This can vary, as some locations are higher than others. I want to use Data Validation on Column H to limit the options to the maximum as dictated by [@LEVELS]. So, theoretically, the range for the data validation could be different for each row. I thought that I could do it w/ the use of an INDIRECT statement, but the Data Validation doesn't seem to like that.

 

[Jeffrey Mahoney]

I'm really hoping you have Office 365. Maybe you should edit your profile.

Here is my first solution to having dynamic Data Validation per row. You would have a supporting formula next to your table or in it and hide the columns. I put the supporting formula in column H. The data validation formula uses =H2# where # is represented to see the SPILL values. The formal transposes the results across rows, so your formula will need to be the right most column in the table. If its an Excel Table, you'll need to allocate enough columns to account for all the levels you have in the list.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Section	Row	Bay	# of Levels	Frame Size	Level of Damaged Frame		Level Data Validation Formulas
2	Section E	ROW 0 - Even	Bay 1	5				Level 1	Level 2	Level 3	Level 4	Level 5
3				6				Level 1	Level 2	Level 3	Level 4	Level 5	Level 6
Sheet1

Cell Formulas
Range	Formula
H2:L2,H3:M3	H2	=TRANSPOSE(FILTER(LevelList,LevelChoices<=D2))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
LevelChoices	=Sheet2!$B$2:$B$19	H2:H3
LevelList	=Sheet2!$C$2:$C$19	H2:H3

Cells with Data Validation
Cell	Allow	Criteria
F2:F3	List	=H2#

Book2
	B	C
1	LevelChoices	LevelList
2	1	Level 1
3	2	Level 2
4	3	Level 3
5	4	Level 4
6	5	Level 5
7	6	Level 6
8	7	Level 7
9	8	Level 8
10	9	Level 9
11	10	Level 10
12	11	Level 11
13	12	Level 12
14	13	Level 13
15	14	Level 14
16	15	Level 15
17	16	Level 16
18	17	Level 17
19	18	Level 18
Sheet2

 

[Jeffrey Mahoney]

It looks like SPILL formulas can't be used in a defined table

You could keep the Formula I put in Cell H3 outside the defined table and it works fine

 

[TheRogue!]

It looks like SPILL formulas can't be used in a defined table

You could keep the Formula I put in Cell H3 outside the defined table and it works fine

Thank you for this. I am unfamiliar w/ the # operator. Is that new for 365? (I'll go research it).

 

[Jeffrey Mahoney]

I'm not sure how long that's been around, maybe Office 16.

Using the pound (#) on a cell with a spill formula will get all the results from the rows or columns that it spills into. This is a dynamic reference