| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
INDIRECT Help
Hi
I need to use INDIRECT but Im not sure how.... =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18="Boo"))*(TRANSMISSION!$ C$32:$IV$32)) The 32 is the value of row(A1)+18 Thanks |
|
#2
|
|||
|
|||
|
=SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18=
"Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Hi I need to use INDIRECT but Im not sure how.... =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(TRANSMISSION!$C$32:$IV$32)) The 32 is the value of row(A1)+18 Thanks |
|
#3
|
|||
|
|||
|
Thanks for the reply
I get a #value when I use your formula. :-( "Bob Phillips" wrote: =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Hi I need to use INDIRECT but Im not sure how.... =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(TRANSMISSION!$C$32:$IV$32)) The 32 is the value of row(A1)+18 Thanks |
|
#4
|
|||
|
|||
|
This works for me
=SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Thanks for the reply I get a #value when I use your formula. :-( "Bob Phillips" wrote: =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Hi I need to use INDIRECT but Im not sure how.... =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(TRANSMISSION!$C$32:$IV$32)) The 32 is the value of row(A1)+18 Thanks |
|
#5
|
|||
|
|||
|
I see the problem but unsure how to fix it....the indirect reference should
be to the current row something like INDIRECT(("TRANSMISSION!$C$"&ROW()+28&":$IV$"&ROW( )+28))) I probably mislead the readers by my explanation. Sorry Thanks again! "Bob Phillips" wrote: This works for me =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Thanks for the reply I get a #value when I use your formula. :-( "Bob Phillips" wrote: =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Hi I need to use INDIRECT but Im not sure how.... =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(TRANSMISSION!$C$32:$IV$32)) The 32 is the value of row(A1)+18 Thanks |
|
#6
|
|||
|
|||
|
the 28 should be 18, but it still doesnt work
"Sandy" wrote: I see the problem but unsure how to fix it....the indirect reference should be to the current row something like INDIRECT(("TRANSMISSION!$C$"&ROW()+28&":$IV$"&ROW( )+28))) I probably mislead the readers by my explanation. Sorry Thanks again! "Bob Phillips" wrote: This works for me =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Thanks for the reply I get a #value when I use your formula. :-( "Bob Phillips" wrote: =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(INDIRECT("TRANSMISSION!$C$"&A1+18&":$IV$" &A1+18))) -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" wrote in message ... Hi I need to use INDIRECT but Im not sure how.... =SUMPRODUCT(--(TRANSMISSION!$C$6:$IV$6="Foo")*(--(TRANSMISSION!$C$18:$IV$18= "Boo"))*(TRANSMISSION!$C$32:$IV$32)) The 32 is the value of row(A1)+18 Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| indirect formula | Excel Worksheet Functions | |||
| Indirect( ) function loosing values when spreadsheets are closed | Excel Worksheet Functions | |||
| is there a NON-volatile version of INDIRECT ?? | Excel Discussion (Misc queries) | |||
| Indirect another wookbook | Excel Worksheet Functions | |||
| Using Indirect & Creating a worksheet Macro | Excel Worksheet Functions | |||
Linear Mode
