Error in Indirect formula

[Kentman]

I have the following formula:

=SUMPRODUCT(--(Hillrise!$AF$2:$AF$2000=$B27),--(Hillrise!$AE$2:$AE$2000=MONDAY))

which works great however, i want to use the information in B26 (which can change) rather than have it hard coded in the formular - but I can't get this to work:

=SUMPRODUCT(--(INDIRECT(B26&"!$AF$2:$AF$2000"=$B27)),--(INDIRECT(B26&"!$AE$2:$AE$2000"=MONDAY)))

It returns #REF

What am I doing wrong?

 

[Andrew Poulsom]

Your parentheses are in the wrong place:

=SUMPRODUCT(--(INDIRECT(B26&"!$AF$2:$AF$2000")=$B27),--(INDIRECT(B26&"!$AE$2:$AE$2000")=MONDAY))

 

[Kentman]

Thanks Andrew - saved my mind!