Conditional validation with only two columns

[Colin]

Hi

I want to be able to have two drop down lists using the data below. If I
select Dog, the second list must present me with Meat and Bones. If I select
Cat, the second list must present me with Meat.

I cannot use named ranged by splitting the columns into Dog Food, Cat Food,
Cow Food etc... They must be in this two-column associated structure.

A B
Dog Meat
Dog Bones
Cat Meat
Cow Grass

Thanks,
Colin

 

[T. Valko]

Try this...

Assume your first drop down is in cell D1.

As the sorce for the dependent drop down use:

=OFFSET(B1,MATCH(D1,A:A,0)-1,,COUNTIF(A:A,D1))

 

[Shane Devenshire]

Hi,

Here is a different method that violates your rule of no name and only 2
lists.

In another sheet create the following ranges
A B C D
List Dog Cow Cat
Dog Meat Grass Meat
Cat Bones
Cow

The top row represents the name to be given to each list. Select A2:A4 and
name it List, select B2:B3 and name it Dog and so on.

Back on the first sheet select cell A1 and choose Data, Validation, List,
and in the source box enter =List

Select cell B1 and choose Data, Validation, List, and in the source box
enter =OFFSET(A1)

You did not explain why you could not use range names.

 

[T. Valko]

enter =OFFSET(A1)

That won't work. Try it like this:

=INDIRECT(A1)

Quality trumps quantity ever time.

 

[Colin]

Thanks Bill. That works Also note this relies on the columns being sorted
by A then B but that is fine for me.

 

[Colin]

Thanks Bill that worked Note that this relies on the columns being sorted
by A then B but thats fine for me.

 

[Colin]

Thanks Shane. I am aware of that method but in my actual sheet I may end up
having about 600 of those, so I wanted a cleaner method rather than create
600 names ranges!

Cheers,
Colin

 

[T. Valko]

You're welcome. Thanks for the feedback!