Keeping Workbook Name Out of Sheet When Copying to Different Book

[navel151]

I run a hockey draft that requires me to copy worksheets into a master sheet
but I need to know how to copy them without the formulas referencing the
workbook that they came from as the formula will only work if it stays
general. i.e. =IF('MasterI12'=1,"Correct", "") rather than
=IF('CopyofSheet4MasterI12'=1,"Correct","").

Right now I have to doa search and replace on every sheet I copy into the
master sheet and it is very painful

Thx.

 

[Dave Peterson]

In xl2003 menus, you can change the link via:
Edit|Links|change links

Another way that I use:

Select the range to copy (in the source worksheet)
Convert the formulas to string
edit|replace
what: = (equal sign)
with: $$$$$= (a nice unused string)
replace all

Now I can copy|paste all these strings and they stay strings.

Then I fix both the source and destination ranges by doing a couple of
edit|replace's.

Select the range to fix
edit|replace
what: $$$$$=
with: =
replace all

 

[Dave Peterson]

That second suggestion will work when you copy the entire sheet, too.

Just select all the cells on the sheet (that's the range to fix) before changing
the formulas to strings.