Copying formulas situation

[Guest]

I know this is not a programming question, however I posted it in the General
questions place to no avail. Any suggestions.


When one has two versions of the same file open, say “frankstaxes.xls†and
“frankstaxes_2.xls†and one is copying a worksheet from one to the other, how
do you get around Excel renaming all the references so that they include the
local file’s filename in many of the formulae??

 

[Dave Peterson]

You don't want to point back at the original workbook?

If yes, you can change your formulas to text, copy the sheet, and then change
those text strings back to formulas.

I do this kind of thing:

Select the sheet to copy
select all the cells on that sheet
edit|Replace
what: =
with: $$$$$=
replace all

Then move/copy the sheet

And reverse the edit|replace--in both spots.

====
be aware that names can hide in lots of places.

I'd use these two tools to verify that I did it ok:

Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

And get Bill Manville's FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm