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" <(E-Mail Removed)> wrote in message
news:0A02F304-FCE0-4A70-B8F0-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
message news:79A1B45A-0CAE-484B-AB8C-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
> message news:79A1B45A-0CAE-484B-AB8C-(E-Mail Removed)...
> > 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 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" <(E-Mail Removed)> wrote in message
> news:0A02F304-FCE0-4A70-B8F0-(E-Mail Removed)...
> > 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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Colin" <(E-Mail Removed)> wrote in message
news:B557ACB4-E161-465F-BF1E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:0A02F304-FCE0-4A70-B8F0-(E-Mail Removed)...
>> > 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
>>
>>
>>