Prev: Hyperlink mapping
Next: Need help for getting output
From: navel151 on 14 Apr 2010 01:16
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.
From: Dave Peterson on 14 Apr 2010 08:01
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
From: Dave Peterson on 14 Apr 2010 08:02
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
 | 
Pages: 1
Prev: Hyperlink mapping
Next: Need help for getting output