Dynamic dropdown list NOT starting in A1 but elsewhere


Please help me with the following dropdown list problem.

I want to start the list (range) in Cell W102 through W120.

As the list grows the dropdown must dynamically update as to avoid blank at the end, but should not go further then W120

 I have tried this formula without any success.: $W$1:INDEX($W:$W,COUNTA($W:$W),1)

 I then changed it to

 $W$102:INDEX($W102:$W120,COUNTA($W102:$W120),1) and, alas also unsuccessful

 Could you possibly help?





Selected Answer

Presuming that you are trying to set up a Data Validation dropdown, please try this formula as a Source in the Data Validation dialog.


The OFFSET() function returns a range, starting at W102, offset 0 rows and 0 columns (meaning really W102) and extends for as many rows as the COUNTA() function returns. An error will result if COUNTA = 0. The OFFSET() function could return a multi-column range. However, for your purpose one columj is enough. 1 is the default value of the omitted Width parameter.

I think you could achieve the same result using the INDEX() function but this looks simpler.


Answer the Question

You must create an account to use the forum. Create an Account or Login