Named Range using INDIRECT

[Will]

Because my range changes each week, I named my ranges
using INDIRECT in the refers to box. This works great
except when I click on the down arrow in the little box
above the row numbers where my named ranges are usually
listed, they aren't there. Also, when I use the named
range as my table array in VLOOKUP in my worksheet, this
works fine. But if I use INDIRECT as my table array in
VLOOKUP, referring to a cell where the named range name
is, I get #REF!.
Other info, - When I click in the little box above the row
numbers and type my range name, it goes right to it.
Thanks for any help.........Will

[Tom Ogilvy]

That behavior is by design. Named ranges which are dynamic (built using
formulas) do not appear in the dropdown. My assumption is that because
there is no guarantee that they are in fact ranges, they are not listed.
(you can have a named range refer to a formula that returns a value rather
than build a range).

the #Ref is because you are using two levels of indirection. generally this
doesn't work.

--
Regards,
Tom Ogilvy

Will <COTT...@COX.NET> wrote in message
news:01fa01c36360$1b33b690$a401...@phx.gbl...