Substituting linked sheets with new sheets by the same name - Excel 2003

[Amiable.Pkunk]

In Sheet2, I've created links to the data in Sheet1.

Now I wish to delete Sheet1, and replace it with a new sheet which I
will name Sheet1 (replacing the whole sheet and not just the data in it
is a neccessity).

However, all the links in Sheet2 are broken... If i fix them, it only
works for the specific new Sheet1; but I need to repeat the process of
replacing the sheet.

Fixing the links AFTER deleting Sheet1 but BEFORE creating the new
Sheet1, also doesn't do - the links, although supposedly not broken and
directing to Sheet1, are still displaying #REF when I create a Sheet1.

Any ideas?


Darkangel

 

[Dave Peterson]

Select all the cells on Sheet2
Edit|replace
what: = (equal sign)
with: $$$$$
replace all

Now all the formulas are just plain old text.

Delete sheet1 and add the new sheet1

Back to sheet2 and make convert those text formulas to real formulas:
Select all the cells
edit|Replace
what: $$$$$
with: =
replace all

If you have formulas on other sheets that refer to sheet1, you'll have to do it
there, too.

And if you have names, charts, ... it could be more difficult.

 

[Bernie Deitrick]

Copy or move your new Sheet1 into the existing workbook. Excel will rename it Sheet1(2). Give it
a new name, like "Sheet3".

Then go to your "Sheet2", select all the cells, and do a replace of "Sheet1" with "Sheet3". Then
delete your existing Sheet1, and rename "Sheet3" as "Sheet1" Note that you may still have problems
with names.

HTH,
Bernie
MS Excel MVP

 

[Amiable.Pkunk]

that's brilliant!

so simple...


!!!thanks