I have a worksheet that I would like to copy to a 3rd worksheet.
One of the formulas in the 2nd worksheet is =Jan!G7. This brings a balance
forward to the next month. So in the 3rd worksheet I would like that cell to
say =Feb!G7 and so on for the year.
There are several of these references throughout the worksheet. I would
like to copy these all at once and paste them to the next worksheet in one
shot.
Is this possible?

After copying the sheet

Easiest might be editreplace

what: Jan

with: Feb

replace all.


Gord Dibben MS Excel MVP


On Mon, 27 Nov 2006 14:55:02 -0800, klnmis
wrote:

I have a worksheet that I would like to copy to a 3rd worksheet.
One of the formulas in the 2nd worksheet is =Jan!G7. This brings a balance
forward to the next month. So in the 3rd worksheet I would like that cell to
say =Feb!G7 and so on for the year.
There are several of these references throughout the worksheet. I would
like to copy these all at once and paste them to the next worksheet in one
shot.
Is this possible?

I have a worksheet that I would like to copy to a 3rd worksheet.
One of the formulas in the 2nd worksheet is =Jan!G7. This brings a
balance
forward to the next month. So in the 3rd worksheet I would like that
cell to say =Feb!G7 and so on for the year.
There are several of these references throughout the worksheet. I
would
like to copy these all at once and paste them to the next worksheet in
one shot.

One way is to use the INDIRECT function instead of Feb!G7.

You’d use another cell in the sheet for the INDIRECT, there’s probably one
already there that’s useful.

For example, suppose cell A1 has a date somewhere in the current month: a
date in February on the Feb sheet, a date in March on the Mar sheet, etc.
Then the carry-forward formula could be:
=INDIRECT(TEXT(DATE(2000,MONTH($A$1)-1,1),"Mmm") & "!G7")