Replace worksheet without affecting the referencing formula

[Jay]

I had a workbook with many worksheets (e.g. WA, WB, WC). Then, in one of the
worksheet, i had formulas that reference to these worksheet (e.g. 'WA'!A1).

I would get updates of the worksheets from my colleagues whom i would latter
update it to the workbook. What i do is
1. Copy all the updated worksheets to the workbook (which it will named as
WA(2), WB(2), WC(2) ).
2. Then, i delete off the original worksheets (except the one with the
formula) and rename WA(2) to WA, WB(2) to WB and etc.

However, the worksheets with the referencing formula doesn't automatically
update itself to the updated worksheet (even though the worksheet name is now
the same of the previous worksheet). How do i ensure that the formula is
automatically updated to the 'new' worksheet name?

Is there any way we could do this?

Thanks.

 

[Dave Peterson]

Can you just select all the cells on the updated worksheet and copy|paste right
over the existing worksheet?

If that doesn't work, then I'd do this:
Select your worksheet.
Change all the formulas to text
select all the cells
Edit|replace
what: = (equal sign)
with: $$$$$=
replace all

Then replace all the worksheets you need to replace.

When you're done, just change all these strings back so that the cells contain
formulas:

Select all the cells
Edit|Replace
what: $$$$$=
with: =
replace all