Data Validation and IF function

I have the attached workbook and I am trying to use Data Validation to fill in the cells H2, H3 and I3 with the information in the Location worksheet. I have setup the States & the City and I am able to get the Data Validation to limit the cities listed based on the selected state. Can I use the IF function in the Data Validation to get cell H3 & I3 empty whenever there is nothing in H2? I tried this =IF(ISTEXT(H2),INDIRECT(H2)," ",) in the source section of the Data Validation window but it is not working.

Also in cell I3, I am trying to get the values to the right of the cities on the Location worksheet. However, some of the values come up correct and others don't. It just keeps whatever value was there previously. Or I just get #N/A like whenever I select "Georgia" & "Atlanta". This happens for a few other cities.

Thanks

You need an event macro in your sheet to clear data validation contents.

Here is an example

The VLOOKUP in I3 should have a 0 or FALSE as it's 4th parameter to find exact match in an unsorted list.

I would normally use something like:

=IF($H3="","",VLOOKUP($H3,Location!$D$3:$E$220,2,FALSE))

You need to make the lookup range absolute, both rows and columns, and the column of the search item absolute if you want to drag across.

As for the Data Validation, the formula would be:

List: =IF($H2="","",INDIRECT(H2))

However, I don't think it will work. Values from dependent lists don't clear when the list they are dependent upon change. I think you'd need VBA to achieve that.

Regards

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save