Data validation list that depends on partially matched data?

Originally Posted by ginganababy

is it something like this? or i got it wrong?


(i made an easy example)

Sorry, not quite what I'm looking for. Here is a sample sheet. What I'm looking for is for the cells under "location #" to be where the user enters a number code. The values to compare with are column A on sheet 2. I want to make the "location name" cells a drop down data validation list, where the list is made up of values from sheet 2, column B that have column A values that match what the user typed in under "location #". So if the user types in 012 under "location #" I want the drop down list under "location name" to have base1 in it, but if the user types in 421 I need the drop down list to have base3 and base4 in it. Also if they type in 901, the list needs to have base6 and base7 in it.

Since the list would be changing, I wasn't sure if the solution would have to be a mix of using vba in worksheet_change() or if there was some other way to do it with a custom data validation using functions I'm not familiar with.

Thanks guys.