Conditional validation with only two columns
 

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

Conditional validation with only two columns
 

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))

--
Biff
Microsoft Excel MVP


"Colin" wrote in message
...
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

Conditional validation with only two columns
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Colin" wrote:

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

Conditional validation with only two columns
 

enter =OFFSET(A1)

That won't work. Try it like this:

=INDIRECT(A1)

Quality trumps quantity ever time.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Colin" wrote:

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

Conditional validation with only two columns
 

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

"T. Valko" wrote:

enter =OFFSET(A1)

That won't work. Try it like this:

=INDIRECT(A1)

Quality trumps quantity ever time.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Colin" wrote:

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

Conditional validation with only two columns
 

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

"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"Colin" wrote in message
...
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

Conditional validation with only two columns
 

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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Colin" wrote:

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

Conditional validation with only two columns
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Colin" wrote in message
...
Thanks Bill that worked :) Note that this relies on the columns being
sorted
by A then B but thats fine for me.

"T. Valko" wrote:

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))

--
Biff
Microsoft Excel MVP


"Colin" wrote in message
...
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