Keeping references while changing worksheets
Select all cells with formulas in sheet "Y"
EditReplace
What: =
With: ZZZ
Replace all.
Delete sheet "X"
Insert new sheet named "X"
Reverse the editreplace.
Gord Dibben MS Excel MVP
On Wed, 25 Nov 2009 18:33:36 GMT, "batu" <u56474@uwe wrote:
Suppose I have an excel workbook with two sheets X and Y.
In X I have a set of data in columns A and B,
1 4
2 5
In Y I want to perform calculations on the data in A,
In cell Y!A1 I have "=X!A1+X!B1", etc.
What I would like to do is delete worksheet X, with the formulas in Y locked
and still refering to X, and insert and new worksheet, rename it X, and have
Y perform the calculations on the new data.
Unfortunately, when I delete X, I get =#REF!A1+#REF!B1 in Y, and can't
recover the reference to worksheet X.
Any suggestions?
Thanks
|