Avatar of Terrygordon
Terrygordon asked on

Dynamically change a data validation list based on a cell value

Hi All. I have a worksheet (Sheet2) that contains lists that are used to populate data validation lists on Sheet1. At the top of each list there is a formula that counts the number of items in the list and the number of the last row in the list.

I would like to dynamically populate the data validation lists on Sheet1, based on the number of the last row in the list.

For example, if the number of the last row in the list in cell B1 on Sheet2 was 7, the list displayed in the dropdown list on Sheet1 would be Sheet2!B2:B7, if the number was 10, the list displayed would be Sheet2!B2:B10, and so on.

Is there any way to include a formula in the data validation source that will change the range of the dropdown list based on the value in Sheet2!B1?

Or maybe there is another way of achieving the same result?

Of course, I could insert a UserForm and use VBA to solve the problem fairly easily, but I'd rather avoid this if possible.

Any help greatly appreciated.



* Data ValidationMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Hi Brad

Thank you. Works perfectly, as usual.


Your help has saved me hundreds of hours of internet surfing.