numeric dropdown, with upper limit defined by another cell

[rjplante]

I would like to create a numeric drop down menu with number values in it (1, 2, 3,...). I have another cell with a value in it (for this example = 6). I would like the numeric value of the drop down to only show values 1-6 when the drop down is selected. Is this possible?

 

[Jeffrey Mahoney]

Here is an example

Book2
	G	H	I	J	K
1	Max Val		Pulldown		List
2	6		4		1
3					2
4					3
5					4
6					5
7					6
Sheet5

Cell Formulas
Range	Formula
K2:K7	K2	=SEQUENCE(MaxVal)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MaxVal	=Sheet5!$G$2	K2

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=K2#
J2	List	=SEQUENCE(H2)

 

[rjplante]

Thanks for the response. I got it to work although I do not know what Cell J2 is used for in the Cells with validation table, nor do I understand why it is =SEQUENCE(H2). What is happening in H2? When I applied the formula =K2# in the Source field for the validated cell, it worked great.

 

[Jeffrey Mahoney]

Oops, I was trying something out and I forgot to erase the cell J2. You got it right

 

[Jeffrey Mahoney]

I don't know why Data Validation can't use Array functions.

 

[rjplante]

Thanks for the explanation. We can all just add that array function in validation fields as one of the thousand things that could be done to make Excel even better.