Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Dynamic dropdown list NOT starting in A1 but elsewhere

0

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?

 Thanks

 Sias

Answer
Discuss

Answers

0
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.

=OFFSET($W$102,0,0,COUNTA($W$102:$W$120))

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.

Discuss


Answer the Question

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