Autopopulate cell based on info selected in Data Validation list

**mintymike** · 12-17-2012, 09:43 AM

Hi everyone. Sorry for flooding the forum (I've just created another thread), but I'm facing two independent problems.

Here I'm wondering if there is a way to automatically populate a cell as soon as previous one (with data validation list) has a value selected?

I know people usually advise to use VLOOKUPS in such cases, however I don't think this applies to my question and let me show you why:

Cell A1 is Category A, Cell B1 is Category B and Cell C1 is Category C. Each of them are data validated lists and so let's say Cat.A is Country, Cat.B is Province, Cat.C is Store size. So I select in A: England, B: Devon and C: 500 (just a random number to give an example). And so after we have all these 3 values, I'd like cell D1 to automatically show: Plymouth. Cell D1 needs all 3 previous cells to have valid data selected, otherwise it stays blank.

I suppose you can't achieve that using VLOOKUP. Is there a way to do it using data validation formulas?

thanks a bunch for any tips.

**Leith Ross** · 12-17-2012, 11:52 AM

Hello mintymike,

You should post your workbook. It will make it easier to provide you with a solution.

How to Attach a sample workbook:

When you are in Advanced reply, click on the paperclip to open the upload window.
View Pic

**6StringJazzer** · 12-17-2012, 12:31 PM

I agree that it would be most helpful to see the file.

To answer this question we need to know how you determine that D1 should be Plymouth. It might very well be possible to do this with formulas without VBA.

**mintymike** · 12-17-2012, 01:26 PM

Yes, you're quite right. I've prepared a sample file and attached it to this reply.

How I determine the cell to be Plymouth (although I've changed cities in the sample) is basic Dependent Data Validation + INDIRECT formula. I've used hints found on Contextures.

I'm wondering if I can achieve what I want using a formula in the Source of the List data validation.

Sample.xlsx

**mintymike** · 12-18-2012, 09:52 AM

Hello, fellows? Any thoughts, please?

**6StringJazzer** · 12-19-2012, 12:42 AM

I've been working with this and I'm sure this is a problem that's been solved many times before in various forms. Do a web search on "excel dependent validation list" for a bunch of information. This article is from a particularly good source.

The strategy involves enumerating all the dependencies. You need a list of all possible countries. For each country you need a list of all possible provinces/states. Then the Province dropdown would be populated based on the country selected; otherwise your user will be able to pick something like USA, Virginia, Liverpool.

Once you have that organized, then one approach is to create an exhaustive list of all valid combinations, each value in a different column starting in B. In A, you create a key field to uniquely identify each row, such as

=B2&"+"&C2&"+"&D2

Then after the user picks all three values, you build a key from that, similar to above. Then you can use VLOOKUP to take the key built from the user choices and look up the key in the list of possible combinations.

It might be easier to show a sample but the data in your example does not correlate country to province to store size to city, a necessary step to do this regardless of method.

Of course, this could all be done in VBA as well, but you still need to lay out all the relationships in your data.