Indirect equivalent for named arrays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

snv - 08 Feb 2008 03:40 GMT

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?

Reply to this Message

T. Valko - 08 Feb 2008 04:21 GMT

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!

Signature

Biff
Microsoft Excel MVP

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

Reply to this Message