External Reference with INDIRECT, INDEX/MATCH
 

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

External Reference with INDIRECT, INDEX/MATCH
 

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

External Reference with INDIRECT, INDEX/MATCH
 

"T. Valko" wrote...
....
** limited by the number of value arguments that CHOOSE will accept which is
version dependent:

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

Then why can I enter

=SUM(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29,30)

which Excel accepts and returns 465? Maybe you mean 30 arguments?

External Reference with INDIRECT, INDEX/MATCH
 

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

External Reference with INDIRECT, INDEX/MATCH
 

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
** limited by the number of value arguments that CHOOSE will accept which
is
version dependent:

Prior to Excel 2007 value arguments = 29
...

Then why can I enter

=SUM(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29,30)

which Excel accepts and returns 465? Maybe you mean 30 arguments?

You've misunderstood what I was referring to about "value arguments".

CHOOSE(index_num,value1,value2,value3...value29)

--
Biff
Microsoft Excel MVP