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.

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.