I have a worksheet that is really two worksheets in one side by side. The
formulas of one of the worksheets refers to cells in the other worksheet. I
would like to move one of these sheets to a different sheet within the
workbook but when I copy it to the other sheet the formulas do not update
correctly. Is there a way to update all of the cell references at the same
time? Here if an example of one of the formulas before it was moved:
=IF(ISERROR(SUM(((B32)/(DAYS360(DATE(2004,12,31),S1,FALSE)))*(360))*(B13) ),"",(SUM(((B32)/(DAYS360(DATE(2004,12,31),S1,FALSE)))*(360))*(B13) )).
Now here is the same formula after the sheet was moved:
=IF(ISERROR(SUM(((#REF!)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*(#REF !)),"",(SUM(((#REF!)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*(#REF !))).
Now here is the correction:
=IF(ISERROR(SUM((('YTD
2005'!B32)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*('YTD
2005'!B13)),"",(SUM((('YTD
2005'!B32)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*('YTD 2005'!B13)))

How can I avoid this problem or correct every formula on the sheet at the
same time?

Here's a guess: You are generating the error by copying columns from the
right on the first sheet and pasting them to the left side of the new sheet,
right?

If you copy a formula from column G that uses a relative reference to column
B, for instance, you'll need to paste it to column G in the new sheet, too.
That will preserve the link & generate no error. Then it's simply a matter
of deleting columns A through F to move the formula over to the left border,
again with no error.


"Bill R" wrote:

I have a worksheet that is really two worksheets in one side by side. The
formulas of one of the worksheets refers to cells in the other worksheet. I
would like to move one of these sheets to a different sheet within the
workbook but when I copy it to the other sheet the formulas do not update
correctly. Is there a way to update all of the cell references at the same
time? Here if an example of one of the formulas before it was moved:
=IF(ISERROR(SUM(((B32)/(DAYS360(DATE(2004,12,31),S1,FALSE)))*(360))*(B13) ),"",(SUM(((B32)/(DAYS360(DATE(2004,12,31),S1,FALSE)))*(360))*(B13) )).
Now here is the same formula after the sheet was moved:
=IF(ISERROR(SUM(((#REF!)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*(#REF !)),"",(SUM(((#REF!)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*(#REF !))).
Now here is the correction:
=IF(ISERROR(SUM((('YTD
2005'!B32)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*('YTD
2005'!B13)),"",(SUM((('YTD
2005'!B32)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*('YTD 2005'!B13)))

How can I avoid this problem or correct every formula on the sheet at the
same time?

I tried your suggestion but I still get the same error. The cells that I am
referencing are on the other sheet now so the cell name should change from
(B32) TO ('YTD 2005'!B32), for example. Any more suggestions to correct all
the addresses on the new sheet all at once, rather than one at a time. Thanks.

"Duke Carey" wrote:

Here's a guess: You are generating the error by copying columns from the
right on the first sheet and pasting them to the left side of the new sheet,
right?

If you copy a formula from column G that uses a relative reference to column
B, for instance, you'll need to paste it to column G in the new sheet, too.
That will preserve the link & generate no error. Then it's simply a matter
of deleting columns A through F to move the formula over to the left border,
again with no error.


"Bill R" wrote:

I have a worksheet that is really two worksheets in one side by side. The
formulas of one of the worksheets refers to cells in the other worksheet. I
would like to move one of these sheets to a different sheet within the
workbook but when I copy it to the other sheet the formulas do not update
correctly. Is there a way to update all of the cell references at the same
time? Here if an example of one of the formulas before it was moved:
=IF(ISERROR(SUM(((B32)/(DAYS360(DATE(2004,12,31),S1,FALSE)))*(360))*(B13) ),"",(SUM(((B32)/(DAYS360(DATE(2004,12,31),S1,FALSE)))*(360))*(B13) )).
Now here is the same formula after the sheet was moved:
=IF(ISERROR(SUM(((#REF!)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*(#REF !)),"",(SUM(((#REF!)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*(#REF !))).
Now here is the correction:
=IF(ISERROR(SUM((('YTD
2005'!B32)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*('YTD
2005'!B13)),"",(SUM((('YTD
2005'!B32)/(DAYS360(DATE(2004,12,31),H1,FALSE)))*(360))*('YTD 2005'!B13)))

How can I avoid this problem or correct every formula on the sheet at the
same time?