INDIRECT Help

[Sandy]

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

 

[Bob Phillips]

=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]

Thanks for the reply
I get a #value when I use your formula. :-(

 

[Bob Phillips]

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]

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!

 

[Sandy]

the 28 should be 18, but it still doesnt work

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!