Let's assume A1 contains a drop down with these selections: Bar, Baz, Foo.

X1:Y1 are the column headers for the dynamic ranges Bar, Baz, Foo.

You want a drop down in B1 based on the selection made in cell A1. If you
select Foo in A1 you want the drop down in B1 to contain the items from te
dynamic range Foo.

As the source for the drop down in B1 use:

=CHOOSE(MATCH(A1,X1:Z1,0),Bar,Baz,Foo)

--
Biff
Microsoft Excel MVP


"Phil" wrote in message
...
T. Valko wrote:
I don't see why you're using INDIRECT in the first place. =Bar, =Baz,
=Foo will work as you noted. Unless you're using another cell to pick
which list to use?

Not exactly - tho that thought had crossed my mind as a future
possibility; I'm already using X1:Z1 as validation for A1:A19, so I just
figured the file would be easier to maintain if that list of lists were
stored in only one place. (BTW, I'd greatly appreciate it if you'd
explain further, in case that possibility becomes a necessity - how
*would* I go about, say, adding validation to column B? :-))

As far as the names not showing up in the name box...

Technically, those formulas used to define the dynamic ranges are *named
formulas*. Named formulas don't show up in the name box.

Okay, I hadn't made that distinction; I just thought "a name is a name".
Thanks for explaining it to me.