Referring to a named range within a formula

[JimKuehl]

Hi,
I'm using the following formula which works fine. "_1" is the name of the array being referenced, and is also typed as a value in cell C4.

=INDEX(_1,MATCH($A7,'1'!$A:$A,0),MATCH($C$3,'1'!$6:$6,0))

As I mentioned the array name "_1" is hard keyed into cell C4. The following, almost identical formula returns a #REF!

=INDEX(C4,MATCH($A7,'1'!$A:$A,0),MATCH($C$3,'1'!$6:$6,0))

How can I fix this?

 

[Rick Rothstein]

Hi,
I'm using the following formula which works fine. "_1" is the name of the array being referenced, and is also typed as a value in cell C4.

=INDEX(_1,MATCH($A7,'1'!$A:$A,0),MATCH($C$3,'1'!$6:$6,0))

As I mentioned the array name "_1" is hard keyed into cell C4. The following, almost identical formula returns a #REF!

=INDEX(C4,MATCH($A7,'1'!$A:$A,0),MATCH($C$3,'1'!$6:$6,0))

How can I fix this?

Instead of C4 use INDIRECT(C4)

 

[JoeMo]

Try replacing C4 with INDIRECT(C4)

 

[Rick Rothstein]

Try replacing C4 with INDIRECT(C4)

Too slow JoeMo

 

[JoeMo]

Too slow JoeMo

DRAT - foiled again!

 

[JimKuehl]

Thanks for the speedy response Rick. And Joe too, I guess

 

[JoeMo]

Thanks for the speedy response Rick. And Joe too, I guess

[Sigh] Thanks for the reluctant recognition.