Indirect equivalent for named arrays
 

I can use the function INDIRECT ("myRange") to create a reference for
my named range.
However, If I use INDIRECT("myArray") to use as a reference to my
named array I get a #REF! error.
Is there a function equivalent to INDIRECT that I can use to reference
named arrays?

Indirect equivalent for named arrays
 

You can use CHOOSE but you're limited to 29 named references in versions of
Excel prior to Excel 2007.

MyArray1
MyArray2

=SUM(CHOOSE(MATCH("MyArray2",{"MyArray1","MyArray2 "},0),MyArray1,MyArray2))

You can use cell refs for the MATCH arguments.

=SUM(CHOOSE(MATCH(A1,B1:B5,0),MyArray1,MyArray2))

INDIRECT returns the #REF! error when the named reference resolves to a
formula. For example:

MyArrayN refers to a dynamic named range like:

=OFFSET(Sheet1!$H$1,,,COUNT(Sheet1!$H:$H))

=SUM(INDIRECT(MyArray2)) = #REF!

--
Biff
Microsoft Excel MVP


"snv" wrote in message
...
I can use the function INDIRECT ("myRange") to create a reference for
my named range.
However, If I use INDIRECT("myArray") to use as a reference to my
named array I get a #REF! error.
Is there a function equivalent to INDIRECT that I can use to reference
named arrays?