Keeping references while changing worksheets
 

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

Keeping references while changing worksheets
 

Before your remove worksheet X, insert your new worksheet, and give it
a name of Z.

Then in worksheet Y highlight all the cells by clicking on the
intersection of the row and column identifiers. Then do CTRL-H (Find &
replace), and:

Find what: X!
Replace with: Z!
Click Replace All

Then you can delete worksheet X, and rename worksheet Z to X if you
really want to.

Hope this helps.

Pete

On Nov 25, 6:33*pm, "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

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

Keeping references while changing worksheets
 

Thanks

Batu

Gord Dibben wrote:
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

Suppose I have an excel workbook with two sheets X and Y.

[quoted text clipped - 15 lines]

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1