Multiple drop down list that automatically update using offset and named ranges

Hello,

I am trying to solve two tasks with drop down lists. Attached is my workbook. There are two attachments, one in .xls and another in .xlsx format.

1) Create a drop down list that automatically updates when NEW DATA is added to the Data worksheet. The drop down list is referencing to data on a different worksheet called "Data" while the drop down is on "List." Since the information is referenced on a different tab, I am using INDRIECT and Named Ranged formula in the data validation.

I have tried using an offset formula within the named range - but this does not work for me.

=OFFSET(Data!A2,0,0,COUNTA(Data!A:A)-1)


2) The City to default to "Blank" once a new State is selected. Currently if you select California and Los Angeles and then change the State to Oregon, Los Angeles will still remain in the City field. I want this field to return to a null field [BLANK].


I've searched and searched all day and found forums that danced around these topics but never together. Any help is much appreciated! Also, if VB is the solution, please over explain the answer as I am an extreme NEWB to VB language.


Thanks!
Angie

Hello
It does seem a little tricky as Excel annoyingly won't allow you to use the INDIRECT function with dynamic named ranges in Data Validation as you require. Also to clear the City Field does require some VBA code. If you're happy to use a User Defined function and a Worksheet Change Event then VBA could solve the problem.

See the attached file. If you select Developer on the Ribbon and then Visual Basic you will see Module 1 contains the UDF and the code to clear the City Field is in the Sheet2 (List) module.

*Note I can't take credit for the UDF, I found it via Google but I'm not sure if it's good form here to provide links to other forums.

Hope this is of some help.
DBY

DBY - thank you so much, your answer solved my questions to this initial post. However I have another question~

I added a 3rd drop down list that is dependent on the 1st and 2nd lists. However this time, the named range is not on the same worksheet as the "Data" tab. I tried duplicating the UDF for this new list but a error results.

State:
City:
County: <--- new drop down list (data is on a different tab than on the first two lists above)


Please see attached.

Hi
Glad it seems to be working. I've added the 3rd dropdown, it just needed the sheet name to be linked dynamically to cell D3. At the moment it only works with Oregon/City Counties as that's all the ranges created. But it should be exapndable as you add more counties and ranges. Just a note. The dynamic ranges I created only reach to row 51, so if you're going to be adding more than 50 cities then you might need to extend those.

Hope it works well.
DBY

this worked perfectly! thank you again!!