External Reference with INDIRECT, INDEX/MATCH

S

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

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
 
H

Harlan Grove

T. Valko said:
** 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?
 
T

T. Valko

Harlan Grove said:
...

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top