Dependent dropdowns

Hi,
XL2003
I have many named ranges in my workbook. Relevent ones a
"Components" "ResTH" (without the quotes).

On my worksheet:
Cell A2, Data Validation dropdown, List source = "Components". From this
list, I select "Res"

Cell B2, Data Validation dropdown, List = TH,SMT. From this list I select "TH"

Cell C2, Data Validation dropdown, List source = INDIRECT(A2&B2). From this
list I can choose items from the ResTH named range.

This works well as long as the ResTH named range is just an ordinary range:
=Data!$V$3:$V$10

However, I would like to change ResTH to a dynamic named range:
=OFFSET(Data!$V$3,0,0,COUNTA(Data!$V$3:$V$100),1)

But when I do, the dropdown in Col C is empty.
Is this a limitation, or am I doing something wrong?

Thanks in advance
Dave.

>Is this a limitation

Not so much a limitation as it is a problem with certain functions. Namely,
INDIRECT. The reference passed to INDIRECT *must* be a text representation
of a valid reference. Using OFFSET to define the dynamic range doesn't
create a valid TEXT representation that INDIRECT can use so =INDIRECT(...)
is evaluating to a #REF! error and thereby not allowing the drop down to
operate.

See these for alternatives:

http://contextures.com/xlDataVal02.html

http://contextures.com/xlDataVal15.html

If you can't get either of those methods to work post back. I know of
another method but it's limited to a specific number of named ranges that
can be referenced.

--
Biff
Microsoft Excel MVP


"Dave" > wrote in message
...
> Hi,
> XL2003
> I have many named ranges in my workbook. Relevent ones a
> "Components" "ResTH" (without the quotes).
>
> On my worksheet:
> Cell A2, Data Validation dropdown, List source = "Components". From this
> list, I select "Res"
>
> Cell B2, Data Validation dropdown, List = TH,SMT. From this list I select
> "TH"
>
> Cell C2, Data Validation dropdown, List source = INDIRECT(A2&B2). From
> this
> list I can choose items from the ResTH named range.
>
> This works well as long as the ResTH named range is just an ordinary
> range:
> =Data!$V$3:$V$10
>
> However, I would like to change ResTH to a dynamic named range:
> =OFFSET(Data!$V$3,0,0,COUNTA(Data!$V$3:$V$100),1)
>
> But when I do, the dropdown in Col C is empty.
> Is this a limitation, or am I doing something wrong?
>
> Thanks in advance
> Dave.

Thanks Biff,
I'll look at that and let you know.
Regards - Dave.

"T. Valko" wrote:

> >Is this a limitation
>
> Not so much a limitation as it is a problem with certain functions. Namely,
> INDIRECT. The reference passed to INDIRECT *must* be a text representation
> of a valid reference. Using OFFSET to define the dynamic range doesn't
> create a valid TEXT representation that INDIRECT can use so =INDIRECT(...)
> is evaluating to a #REF! error and thereby not allowing the drop down to
> operate.
>
> See these for alternatives:
>
> http://contextures.com/xlDataVal02.html
>
> http://contextures.com/xlDataVal15.html
>
> If you can't get either of those methods to work post back. I know of
> another method but it's limited to a specific number of named ranges that
> can be referenced.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Dave" > wrote in message
> ...
> > Hi,
> > XL2003
> > I have many named ranges in my workbook. Relevent ones a
> > "Components" "ResTH" (without the quotes).
> >
> > On my worksheet:
> > Cell A2, Data Validation dropdown, List source = "Components". From this
> > list, I select "Res"
> >
> > Cell B2, Data Validation dropdown, List = TH,SMT. From this list I select
> > "TH"
> >
> > Cell C2, Data Validation dropdown, List source = INDIRECT(A2&B2). From
> > this
> > list I can choose items from the ResTH named range.
> >
> > This works well as long as the ResTH named range is just an ordinary
> > range:
> > =Data!$V$3:$V$10
> >
> > However, I would like to change ResTH to a dynamic named range:
> > =OFFSET(Data!$V$3,0,0,COUNTA(Data!$V$3:$V$100),1)
> >
> > But when I do, the dropdown in Col C is empty.
> > Is this a limitation, or am I doing something wrong?
> >
> > Thanks in advance
> > Dave.
>
>
> .
>

Hi Biff,
I looked at both the links you sent, but still couldn't figure out how to
concatonate the values of 2 cells, which themselves were derived from dynamic
lists, to make a name for a third dropdown.
So I went back to non-dynamic named ranges, but used a sheet-change event
macro to re-define any named range that was modified.
This worked well.

Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(2, Target.Column) = "" Then Exit Sub
A = Target.Column
B = Cells(100, A).End(xlUp).Row
ActiveWorkbook.Names.Add Name:=Cells(2, A), RefersTo:=Range(Cells(3, A),
Cells(B, A))
End Sub

Regards - Dave.


"T. Valko" wrote:

> >Is this a limitation
>
> Not so much a limitation as it is a problem with certain functions. Namely,
> INDIRECT. The reference passed to INDIRECT *must* be a text representation
> of a valid reference. Using OFFSET to define the dynamic range doesn't
> create a valid TEXT representation that INDIRECT can use so =INDIRECT(...)
> is evaluating to a #REF! error and thereby not allowing the drop down to
> operate.
>
> See these for alternatives:
>
> http://contextures.com/xlDataVal02.html
>
> http://contextures.com/xlDataVal15.html
>
> If you can't get either of those methods to work post back. I know of
> another method but it's limited to a specific number of named ranges that
> can be referenced.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Dave" > wrote in message
> ...
> > Hi,
> > XL2003
> > I have many named ranges in my workbook. Relevent ones a
> > "Components" "ResTH" (without the quotes).
> >
> > On my worksheet:
> > Cell A2, Data Validation dropdown, List source = "Components". From this
> > list, I select "Res"
> >
> > Cell B2, Data Validation dropdown, List = TH,SMT. From this list I select
> > "TH"
> >
> > Cell C2, Data Validation dropdown, List source = INDIRECT(A2&B2). From
> > this
> > list I can choose items from the ResTH named range.
> >
> > This works well as long as the ResTH named range is just an ordinary
> > range:
> > =Data!$V$3:$V$10
> >
> > However, I would like to change ResTH to a dynamic named range:
> > =OFFSET(Data!$V$3,0,0,COUNTA(Data!$V$3:$V$100),1)
> >
> > But when I do, the dropdown in Col C is empty.
> > Is this a limitation, or am I doing something wrong?
> >
> > Thanks in advance
> > Dave.
>
>
> .
>

Good deal. Thanks for feeding back!

--
Biff
Microsoft Excel MVP


"Dave" > wrote in message
...
> Hi Biff,
> I looked at both the links you sent, but still couldn't figure out how to
> concatonate the values of 2 cells, which themselves were derived from
> dynamic
> lists, to make a name for a third dropdown.
> So I went back to non-dynamic named ranges, but used a sheet-change event
> macro to re-define any named range that was modified.
> This worked well.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Cells(2, Target.Column) = "" Then Exit Sub
> A = Target.Column
> B = Cells(100, A).End(xlUp).Row
> ActiveWorkbook.Names.Add Name:=Cells(2, A), RefersTo:=Range(Cells(3, A),
> Cells(B, A))
> End Sub
>
> Regards - Dave.
>
>
> "T. Valko" wrote:
>
>> >Is this a limitation
>>
>> Not so much a limitation as it is a problem with certain functions.
>> Namely,
>> INDIRECT. The reference passed to INDIRECT *must* be a text
>> representation
>> of a valid reference. Using OFFSET to define the dynamic range doesn't
>> create a valid TEXT representation that INDIRECT can use so
>> =INDIRECT(...)
>> is evaluating to a #REF! error and thereby not allowing the drop down to
>> operate.
>>
>> See these for alternatives:
>>
>> http://contextures.com/xlDataVal02.html
>>
>> http://contextures.com/xlDataVal15.html
>>
>> If you can't get either of those methods to work post back. I know of
>> another method but it's limited to a specific number of named ranges that
>> can be referenced.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Dave" > wrote in message
>> ...
>> > Hi,
>> > XL2003
>> > I have many named ranges in my workbook. Relevent ones a
>> > "Components" "ResTH" (without the quotes).
>> >
>> > On my worksheet:
>> > Cell A2, Data Validation dropdown, List source = "Components". From
>> > this
>> > list, I select "Res"
>> >
>> > Cell B2, Data Validation dropdown, List = TH,SMT. From this list I
>> > select
>> > "TH"
>> >
>> > Cell C2, Data Validation dropdown, List source = INDIRECT(A2&B2). From
>> > this
>> > list I can choose items from the ResTH named range.
>> >
>> > This works well as long as the ResTH named range is just an ordinary
>> > range:
>> > =Data!$V$3:$V$10
>> >
>> > However, I would like to change ResTH to a dynamic named range:
>> > =OFFSET(Data!$V$3,0,0,COUNTA(Data!$V$3:$V$100),1)
>> >
>> > But when I do, the dropdown in Col C is empty.
>> > Is this a limitation, or am I doing something wrong?
>> >
>> > Thanks in advance
>> > Dave.
>>
>>
>> .
>>