The only good way to accomplish this is to create a vertical list of your
worksheets somewhere (maybe outside the print area in like column AA), then
use INDIRECT in your formula.

So in AA4, type "Wk1" without quotes, then drag AA4's fill handle down as
many rows as you need and the auto-fill will increment Wk1 to Wk2, Wk3, etc.
as desired without having to do a bunch of typing.

Now change your formula in C4 as follows:
= SUMIFS(INDIRECT("'[WKLY YR06.xlsx]"&AA4&"'!$C$2:$C$5000"),INDIRECT("'[WKLY
YR06.xlsx]"&AA4&"'!$E$2:$E$5000"),"TF5",INDIRECT("'[WKLY
YR06.xlsx]"&AA4&"'!$D$2:$D$5000"),"600020")


--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Kim" wrote:

On Jan 26, 2:39 pm, KC hotmail com <kcrippstein<atdot wrote:
Just copy & paste the formula as you have described, then change the 3
instances of Wk1 to Wk2

--
Please remember to indicate when the post is answered so others can benefit
from it later.



"Kim" wrote:
I want to copy a formula from one cell to another but need the sheet
reference to change from "sheet 1" to "sheet 2" in the formula:

Example = SUMIFS('[WKLY YR06.xlsx]Wk1'!$C$2:$C$5000,'[WKLY YR06.xlsx]
Wk1'!$E$2:$E$5000,"TF5",'[WKLY YR06.xlsx]Wk1'!$D$2:$D$5000,"600020")

This formula is in cell C4, I want to copy to D4 and I want everything
to stay the same but go to the next sheet (same place in the sheet).
Can anyone help me???

Kim
.- Hide quoted text -

- Show quoted text -

I know I can copy and paste but I want the sheet Wk1 to automatically
change to Wk2 and so forth
.