| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"))) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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"))) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| indirect w/ vlookup = #REF error | Excel Worksheet Functions | |||
| help me(indirect function error) | Excel Programming | |||
| Error when using Indirect | Excel Worksheet Functions | |||
| Error Embedding Row() within Indirect() | Excel Worksheet Functions | |||
| INDIRECT function error | Excel Discussion (Misc queries) | |||
Linear Mode
