changing the validations to =INDIRECT($X$1)

The problem with that is INDIRECT won't work when referencing a dynamic
range.

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? If that's the case then it gets a bit more complicated.

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.

--
Biff
Microsoft Excel MVP


"Phil" wrote in message
...
T. Valko wrote:
X1 = Bar
Y1 = Baz
Z1 = Foo
[...]
Select cell E1
Goto the menu DataValidation
Allow: List
Source:

=X2:INDEX(X2:X100,COUNTIF(X2:X100,"*"))

OK

Repeat this for cells E2 and E3.

Source for E2:

=Y2:INDEX(Y2:Y100,COUNTIF(Y2:Y100,"*"))

Source for E3:

=Z2:INDEX(Z2:Z100,COUNTIF(Z2:Z100,"*"))

Okay, that worked great, exactly what I wanted - but then I started
building on that based on advice I found elsewhere. Instead of using
these formulas in the data validation directly, I used them as the targets
of named ranges called Foo, Bar, and Baz, and set the validation sources
to =Foo, =Bar, and =Baz.

That worked also, but then I tried changing the validations to
=INDIRECT($X$1) etc., and it replied "The Source currently evaluates to an
error. Do you wish to continue?" and when I said "Yes", my pick lists
ended up empty. Any clue why? And any clue why Foo, Bar, and Baz don't
show up in the list of named ranges at the top left of the screen, either,
when "Insert Name" shows them plain as day?