Using Indirect in Sumproduct formula

[Kleev]

I have the following formula which is working:
=SUMPRODUCT(--('Wk41-42'!$C$6:$C$1474=$A6),--('Wk41-42'!$D$6:$D$1474<>""),--('Wk41-42'!$L$6:$L$1474<12),--('Wk41-42'!$L$6:$L$1474>=10))
In cells above this formula, I have labels for the weeks involved, ie 35-36,
37-38, 39-40, and 41-42. I would like to use the value in the cell
concantenated with the string "Wk" to be able to change the formula so that I
can copy the formula and have it work without having to change the sheet
names to get it to work.
I tried the following, which is obviously incorrect as I get a #REF error.
=SUMPRODUCT(--(INDIRECT("'Wk" & I$4 &
"'!$C$6:$C$1474")=$A6),--('Wk41-42'!$D$6:$D$1474<>""),--('Wk41-42'!$L$6:$L$1474<12),--('Wk41-42'!$L$6:$L$1474>=10))
Can someone help me out? Thanks.

[Kleev]

I found the problem. After looking at the post, I noticed I was using I4 for
the cell, but my formulas were on row 6. I double checked, and sure enough,
I needed to use I$5. Boy is my face red.

"Kleev" wrote:

> I have the following formula which is working:
> =SUMPRODUCT(--('Wk41-42'!$C$6:$C$1474=$A6),--('Wk41-42'!$D$6:$D$1474<>""),--('Wk41-42'!$L$6:$L$1474<12),--('Wk41-42'!$L$6:$L$1474>=10))
> In cells above this formula, I have labels for the weeks involved, ie 35-36,
> 37-38, 39-40, and 41-42. I would like to use the value in the cell
> concantenated with the string "Wk" to be able to change the formula so that I
> can copy the formula and have it work without having to change the sheet
> names to get it to work.
> I tried the following, which is obviously incorrect as I get a #REF error.
> =SUMPRODUCT(--(INDIRECT("'Wk" & I$4 &
> "'!$C$6:$C$1474")=$A6),--('Wk41-42'!$D$6:$D$1474<>""),--('Wk41-42'!$L$6:$L$1474<12),--('Wk41-42'!$L$6:$L$1474>=10))
> Can someone help me out? Thanks.