Dynamic list

[M0X3]

I'm trying to create a data validation list that dynamically adjusts based on the cell above it. The cell above will have the name of the tab that we want to get this list from, but the problem is the list has a different length in each tab. I know name manager and INDIRECT would help accessing the different tabs but how would I have the list increase or decrease in size dynamically? Would creating a table out of the list be enough?

 

[Toadstool]

Hi M0X3,

You could have each tab maintain the list length in a specific cell then retrieve using INDIRECT, or maybe you can COUNTA the size of the list.

Book1
	H	I
1	Sheet2	Sheet3
2	5	3
Sheet1

Cell Formulas
Range	Formula
H2:I2	H2	=COUNTA(INDIRECT("'"&H1&"'!$e$2:$e$999"))

 

[M0X3]

Hi M0X3,

You could have each tab maintain the list length in a specific cell then retrieve using INDIRECT, or maybe you can COUNTA the size of the list.

Book1
	H	I
1	Sheet2	Sheet3
2	5	3
Sheet1

Cell Formulas
Range	Formula
H2:I2	H2	=COUNTA(INDIRECT("'"&H1&"'!$e$2:$e$999"))

Brilliant! I have yet to test it out but this definitely seems like it'll work. Thanks

 

[Toadstool]

Brilliant! I have yet to test it out but this definitely seems like it'll work. Thanks

You're welcome and thanks for the feedback.