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

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

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

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

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

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

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

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