Using INDIRECT to reference a sheet in a formula 1

[mpm32]

I have this formula and it works well;

=SUMPRODUCT(-('Sheet1'!$D$5:$O$5<=$A$7),'Sheet1'!D9:O9)*-1

I have a cell A3 with the sheet name in it, when I try to use INDIRECT in this formula, I keep getting a #ref#. I know it's something simple like a missing ) or '.

Can anyone help correct this formula?


=SUMPRODUCT(-(INDIRECT($A$3&"!$D$5:$O$5"<=$A$7),INDIRECT($A$3&"!D9:O9")*-1

Thanks in advance!

 

[SkipVought]

hi,
[tt]
=SUMPRODUCT(-(INDIRECT($A$3&"!$D$5:$O$5")<=$A$7)*(INDIRECT($A$3&"!D9:O9")))*-1
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Awesome works great, I love this forum!

Thanks, Skip

 

[SkipVought]

but why the NEG NEG?
[tt]
=SUMPRODUCT((INDIRECT($A$3&"!$D$5:$O$5")<=$A$7)*(INDIRECT($A$3&"!D9:O9")))
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Hmm, not sure those must have been left over from my previous attempts at the formula, I took them out.

Thanks!