Data validation list that depends on partially matched data?

**stikxs** · 03-10-2010, 06:55 PM

I have 2 worksheets, one has a list of zip codes in column A (all unique) and names in column B, and the other sheet will have the user entering a zip code in column A and column B needs a data validation list that only contains rows from sheet 1 that have a zip code matching the user input, but I need all of the partial matches in the drop down list. So if the user types 385 in A1, B1 will have a drop down list with all of the rows in the zip code list that start with 385. I found a lot of sites on google that talk about making one list that is dependent on the selection of another list, but didn't see anything quite like this.

Post 4 has my sample sheet/expected results.

**JBeaucaire** · 03-10-2010, 07:17 PM

Hi Stikxs, welcome to the forum.

Make it easy for us to help you by mocking a sample workbook showing the data and your desired results, point out where the results came from if not obvious in your sample data. Don't oversimplify the sample data.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**ginganababy** · 03-10-2010, 08:01 PM

Originally Posted by stikxs

I have 2 worksheets, one has a list of zip codes in column A (all unique) and names in column B, and the other sheet will have the user entering a zip code in column A and column B needs a data validation list that only contains rows from sheet 1 that have a zip code matching the user input, but I need all of the partial matches in the drop down list. So if the user types 385 in A1, B1 will have a drop down list with all of the rows in the zip code list that start with 385. I found a lot of sites on google that talk about making one list that is dependent on the selection of another list, but didn't see anything quite like this.

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

(i made an easy example)

**stikxs** · 03-11-2010, 05:22 PM

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.

**JBeaucaire** · 03-11-2010, 07:35 PM

These are called Dependent Drop Down lists. I've set up named ranges on Sheet 2 so that you can use those ranges in Data validation lists. Have a look.

**stikxs** · 03-12-2010, 10:56 AM

That's close but I was aiming to only have 1 drop down, in the "location name". The values in the drop down would change with what the user has typed in the adjacent cell under "location #".