Thanks for your help with this. I'll check it out.
--
Scott S


"T. Valko" wrote:

INDIRECT requires a *text* representation of a valid reference. Your dynamic
range does not meet this requirement.

I don't know what kind of formula you're using a dynamic range in but this
is how to get it to work in a simple SUM formula.

Assume cell A1 is your drop down list. rng1 is the named dynamic range.

If you have only that single named range:

=SUM(CHOOSE(MATCH(A1,A1,0),rng1))

If have a few named ranges:

=SUM(CHOOSE(MATCH(A1,{"rng1","rng2",rng3"},0),rng1 ,rng2,rng3))

If you have many** named ranges list the names in a range of cells:

J1 = rng1
J2 = rng2
J3 = rng3

=SUM(CHOOSE(MATCH(A1,J1:J3,0),rng1,rng2,rng3))

** limited by the number of value arguments that CHOOSE will accept which is
version dependent:

Prior to Excel 2007 value arguments = 29
Excel 2007 value arguments = 254


--
Biff
Microsoft Excel MVP


"ScottS" wrote in message
...
I have a large spreadsheet with each tab organized as a table, each table
has
a dynamic named range.

My users want to access data from this main source by selecting a range
name
from a list (cell with dropdown list). I have been able to get this to
work
in one workbook by using INDIRECT to capture the range name and
INDEX\MATCH
to bring in the actual cell contents.

When I try to do this with INDIRECT I get the REF# error. Is this
something
that can be done?

Thanks in advance for any help or suggestions.
--
Scott S