|
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 |
=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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com