External Reference with INDIRECT, INDEX/MATCH

[ScottS]

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.

 

[T. Valko]

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

 

[Harlan Grove]

** 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?

 

[ScottS]

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

 

[T. Valko]

...

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)