Dependent dropdowns

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,
XL2003
I have many named ranges in my workbook. Relevent ones are:
"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.
 
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.
 
Back
Top