Indirect - error

[Kashyap]

Hi,

=SUMPRODUCT((Hiren!B4:B1000=B10)*(Hiren!C4:C1000=C10)*(Hiren!I4:I1000=L1)*Hiren!G4:G1000)

I'm trying above formula with indirect as below but I'm not getting result
from below formula..

Where am I going wrong?

=SUMPRODUCT((INDIRECT("'"&H10&"'!B4:B1000"=B10))*(INDIRECT("'"&H10&"'!C4:C1000"=C10))*(INDIRECT("'"&H10&"'!I4:I1000"=$L$1))*(INDIRECT("'"&H10&"'!G4:G1000")))

 

[Patrick Molloy]

=SUMPRODUCT( (INDIRECT(H10&"'!B4:B1000")=B10)
*(INDIRECT(H10&"'!C4:C1000")=C10)
*(INDIRECT(H10&"'!I4:I1000")=$L$1)
*(INDIRECT(H10&"'!G4:G1000"))
)

 

[Dave Peterson]

A typo warning.

That leading apostrophe was lost in your suggestion, Patrick.

Maybe...

=SUMPRODUCT((INDIRECT("'"&H10&"'!B4:B1000")=B10)
*(INDIRECT("'"&H10&"'!C4:C1000")=C10)
*(INDIRECT("'"&H10&"'!I4:I1000")=$L$1)
*(INDIRECT("'"&H10&"'!G4:G1000")))

 

[Patrick Molloy]

it was supposed not to be there. my indirect worked without -- at last in my
workbook!

 

[Patrick Molloy]

however, having checked - obviously you're correct. Thats bizarre since i
just cut/pasted into the mail the formula that i had, which definitely worked
- i wouldn't have posted otherwise.
However, without the apostrophe, i get the expected #Ref error. Hmmmm

apologies & thanks

Patrick