Dynamic data validation

[WaltW]

I have a sorted master list containing 3 columns that I want to use for data validation. Here's the issue I'm trying to resolve: I have three cells that need data validation - in Cell 1, any valid entry from column A can be selected. The entry in Cell 2 is then restricted by what's entered in Cell 1. In turn, the entry in Cell 3 is restricted by what's entered in Cell 1 AND Cell 2. Is there any way to dynamically change data validation for cells 2 and 3 as entries are made in cells 1 and 2?

I've attached an example master list to hopefully help illustrate if it's not clear. Using the attached list, you would be limited to "A" or "B" in cell 1. If you select "B" in cell 1, you would be limited only to "4" or "5" in cell 2. And if you select "4" in cell 2, you would be limited only to items "Item B41" through "Item B47" in cell 3.

Please let me know if further clarification is needed. Any suggestions you could give me to accomplish this would be hugely appreciated. Thanks!

-Walt

 

[AnotherHiggins]

Excel -- Data Validation -- Create Dependent Lists

Dependent Validation. Excel Data Validation List Based on the Value of Another

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[WaltW]

Thanks for these references. I've used one of them before to create dependent data validation based on a single cell, but I'm having trouble adapting it to make data validation dependent on TWO cell entries. I'd appreciate any help or direction you can give me in that area ...

Thanks!
-Walt

 

[SkipVought]

You just extend the concept.

When you TRY, "but I'm having trouble adapting it " what are you trying and what are the results?

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[AnotherHiggins]

-> I'm having trouble adapting it to make data validation dependent on TWO cell entries

But the second cell is already dependent on the first cell. So by the time you are down to that level, the first value is already known. For example, the second dropdown can't be "Apple" unless the first dropdown is already "Fruit".

So, as Skip says, you just extend the concept. If the validation for B2 is "=indirect(A2)", then the validation for C2 would just be "=indirect(B2)".

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

To go two deep, the data must support as well...
[tt]
fruit apple delicious
fruit apple granny smith
fruit apple macintosh
fruit pear bartlett
fruit pear other
[/tt]


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[WaltW]

Sorry for being so dense, but it's still not clear to me. A better analogy for my data goes something like this:

Car Chevrolet Impala
Car Chevrolet Malibu
Car Dodge Challenger
Car Dodge Charger
Car Dodge Viper
Minivan Chevrolet Uplander
Minivan Dodge Grand Caravan

The way I understand it, there are 2 methods you can use: the INDIRECT() method and the OFFSET() method. With the INDIRECT() method, don't I need separate named lists for each type of data validation? So with this kind of data wouldn't I end up with multiple lists with the same name (like "Chevrolet")? I also end up with multiple occurrences of the same selection in the validation list (e.g. two of "Chevrolet" and three of "Dodge" when I select "Car"). The OFFSET() method looks more like what I would want to use, but getting the list definitions right in the data validations for the second level are more complicated than my simple mind can figure out ...

Hopefully I'm just making things too complicated, and somebody can simplify it for me. Thanks!

-Walt

 

[SkipVought]

There is one table with column ranges for
[tt]
VehicleType
Make
Model
[/tt]
I like using Named Ranges.

The only reason for using the INDIRECT function is to isolate the cell reference, which I do not believe will be an issue.

Get familiar with the OFFSET function, the MATCH function the COUNTIF function. The OFFSET function returns a range, so you use the MATCH function to find where the lookup value occurs in the referenced range (argument 2) and the COUNTIF fucntion to define the range depth (argument 4). Argument 2 is the column offset for the anchor and argument 5 is the width of the range, which is 1 in this instance.

You just use this EXACT SAME APPROCH to drill down, realizing that the subsequent ranges will each be expressed in an OFFSET formula.


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[WaltW]

Thanks for the tips, Skip. I'm sure you're right that the main issue for me is I'm unfamiliar with the OFFSET and MATCH functions. So I'll check out those functions more closely and get it figured out. Thanks very much for your time and help.

Thanks to John too.

-Walt

 

[AnotherHiggins]

-> So with this kind of data wouldn't I end up with multiple lists with the same name (like "Chevrolet")?

I see your point, but no.

The sheet with all of your lists would look something like this, with the header row also being the name of the range:

[b]
VehicleType   Car      Minivan   CarChevy    CarDodge   MinivanChevy    MinivanDodge[/b]
Car          Chevy      Chevy     Impala    Challenger    Uplander      Grand Caravan
Minivan      Dodge      Dodge     Malibu     Charger
                                  Viper

Notice that once you get to the "third level", you are using longer names made up of the first two names (like MinivanChevy).

So on the the sheet with the validation, your "sources" would look like this:

ColumnA: =VehicleType
ColumnB: =INDIRECT(A2)
ColumnC: =INDIRECT(A2&B2)

"A2&B2" is called concatenation. It is a way to build strings. (In fact, CONCATENATE is an Excel function, but I think it's easier to just use an ampersand [&]).

So if your first two selections are "Minivan" and "Chevy", the "=INDIRECT(A2&B2)" in column C puts those together and forms "MinivanChevy". Good news! That's the name of one of our ranges.

- - -

And to help get you started on the OFFSET function, Let's say that VehicleType is in column A on a sheet named "LISTSHEET".

Go to Insert > Name > Define, type in the name of the range (VehicleType in this case). In the refers to box, the formula would be:
[tab][COLOR=blue white]=offset(ListSheet!$A$1, 1, 0, counta(ListSheet!$A:$A) - 1, 1)[/color]

You can look in Excel's help file for info on the Offset and Counta functions are if you need.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[WaltW]

Thank you, John. Haven't had time to work on this again, but what you posted here definitely clears up some of my confusion and gives me a good direction to go in. Thanks for the help and your time.

-Walt

 

[SkipVought]

There are really several different ways. On occasion I use MS Query, where one selection becomes a criteria value in a query, the results of which are the next deeper list.

faq68-5829.


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]