Referencing a named range in a formula

[JV0710]

Hello

Please could someone help me with the following query:

I have in my worksheet, the following formula in cell C4
=SUMPRODUCT(--(Cluster=Report!C$2)*(Month=Report!$A$1)*(Hierarchy))

in cell A4 - is the name of the range that I want to sum " Hierarchy"

I want to refer to the named range "Hierarchy" in the formula as a cell reference
e.g.
=SUMPRODUCT(--(Cluster=Report!C$2)*(Month=Report!$A$1)*(indirect("range"&A4))
or
=SUMPRODUCT(--(Cluster=Report!C$2)*(Month=Report!$A$1)*(A4))

both these methods give me errors . . . Is there a way to do this ?

Thanks

JVN

 

[Andrew Poulsom]

Just INDIRECT(A4) should do the job.

 

[MrKowz]

Edit - Nevermind

 

[JV0710]

Hi Andrew

Thanks for your answer.

I tried that as well
=SUMPRODUCT(--(Cluster=Report!C$2)*(Month=Report!$A$1)*(indirect(A4))
and I kept getting an error #Value

and then excel ran out of resources to calculate

I rebooted and started again in a new workbook and now it works

Thank you once again

Joe