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