Keeping Workbook Name Out of Sheet When Copying to Different Book
 

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.

Keeping Workbook Name Out of Sheet When Copying to Different Book
 

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



navel151 wrote:

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

Keeping Workbook Name Out of Sheet When Copying to Different Book
 

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.

navel151 wrote:

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