Help on formulas to refer to previous sheet when I copy it?

**Moeshell** · 10-14-2011, 11:03 AM

I am trying to get a new copied sheet to refer to the previous sheet when I copy it to keep a cumulative of vendors for each day.
In the attached example, if I copy the Sheet “Day 2” and rename it “Day 3” is there a way to get it to change the formulas so that they reference “Day 2” instead of referring back to “Day 1”.
In the cell A47, I’ve tried to do a formula that excel doesn’t seem to like.
Any suggestions?

**NBVC** · 10-14-2011, 11:07 AM

The easiest way is to do and Edit|Replace and replace Day 1 with Day 2

**Moeshell** · 10-14-2011, 11:27 AM

Thanks for the suggestion. I've thought of that too, but this is for an engineer that I am working with and he wants as few steps as possible.
He said that there is a way. He's done it before where he just copied the sheet and it has done it automatically.
I'm not sure if there is a built in excel function that i'm just not familar with. I've tried the indirect function but that didn't seem to work with trying to reference to another sheet, or maybe I just didn't understand it well enough to get it to work.

**NBVC** · 10-14-2011, 11:34 AM

How about in D47:

=INDIRECT("'Day "&$F$15-1&"'!D"&ROW(D47))

copied down and in H47

=F47+INDIRECT("'Day "&$F$15-1&"'!H"&ROW(F47))

copied down

assuming F15 corresponds to sheet Day #