Indirect - error
 

Hi,

=SUMPRODUCT((Hiren!B4:B1000=B10)*(Hiren!C4:C1000=C 10)*(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:G1 000")))

Indirect - error
 

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



"Kashyap" wrote:

Hi,

=SUMPRODUCT((Hiren!B4:B1000=B10)*(Hiren!C4:C1000=C 10)*(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:G1 000")))

Indirect - error
 

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 wrote:

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

"Kashyap" wrote:

Hi,

=SUMPRODUCT((Hiren!B4:B1000=B10)*(Hiren!C4:C1000=C 10)*(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:G1 000")))



--

Dave Peterson

Indirect - error
 

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

"Dave Peterson" wrote:

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 wrote:

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

"Kashyap" wrote:

Hi,

=SUMPRODUCT((Hiren!B4:B1000=B10)*(Hiren!C4:C1000=C 10)*(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:G1 000")))



--

Dave Peterson

Indirect - error
 

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

"Patrick Molloy" wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Kashyap" wrote:

Hi,

=SUMPRODUCT((Hiren!B4:B1000=B10)*(Hiren!C4:C1000=C 10)*(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:G1 000")))



--

Dave Peterson