How to copy a formula without file reference

J

jijy

I have a spreadsheet with formulas lnking cells in different sheets o
the same file. When I copy this formula into another spreadsheet , i
retains the file reference thus making it useless.

Eg. When this formula - (='Sheet 1'!S1420) - is copied to another fil
the formula changes as follows: ='[2006 Plan.xls]Sheet 1'!S1420, wher
"2006 Plan.XLS" is the file name of the first file.

I need to copy this formula to over 60 files running Excel 2000 o
Excel 2003.

Please help me with this issue.

Thanks,

Jij
 
S

Sukhjeet

jijy: What I have done to cirumvent this problem is to copy all the formulae
in this sheet, and then search and replace the filename with nothing.
For example, in your example, search for [2006 Plan.xls] and leave the
"Replace with" blank. This should do it.
Sukhjeet
 
E

Earl Kiosterud

jijy,

Since you're apparently copying the formula to the same cell location in the
other workbook (apparently, since it isn't changing the cell reference), you
might find that copying the text of the formula, rather than the entire cell
as an object, will work for you. Select the text of the formula in the
formula bar and copy that (be sure to press Esc at this point to cancel your
edit of the source cell), then go to the target and paste it. It will
paste, unchanged. This is a literal copy/paste.
 
D

Dave Peterson

If I have one formula to copy|paste, then I use Earl's technique.

But if I have lots of formulas to copy, I change my formulas to text, copy|paste
(as text), then change the text back to formulas.

In the original worksheet
select the range to copy
edit|replace
what: = (equal sign)
with: $$$$$=
replace all

Copy and paste to where I want

Then change them back:
Select the pasted range
edit|replace
what: $$$$$=
with: =
replace all

(In both the "sending" and "receiving" worksheets.)
I have a spreadsheet with formulas lnking cells in different sheets of
the same file. When I copy this formula into another spreadsheet , it
retains the file reference thus making it useless.

Eg. When this formula - (='Sheet 1'!S1420) - is copied to another file
the formula changes as follows: ='[2006 Plan.xls]Sheet 1'!S1420, where
"2006 Plan.XLS" is the file name of the first file.

I need to copy this formula to over 60 files running Excel 2000 or
Excel 2003.

Please help me with this issue.

Thanks,

Jijy
Click to expand...
 
E

Earl Kiosterud

Dave,

That's a good suggestion for en masse copy/pastes. We need a "Paste
literal" added to paste special! We also need a NUL value, and a million
other things. I'd better not get started.
--
Earl Kiosterud
www.smokeylake.com

Dave Peterson said:
If I have one formula to copy|paste, then I use Earl's technique.

But if I have lots of formulas to copy, I change my formulas to text,
copy|paste
(as text), then change the text back to formulas.

In the original worksheet
select the range to copy
edit|replace
what: = (equal sign)
with: $$$$$=
replace all

Copy and paste to where I want

Then change them back:
Select the pasted range
edit|replace
what: $$$$$=
with: =
replace all

(In both the "sending" and "receiving" worksheets.)
I have a spreadsheet with formulas lnking cells in different sheets of
the same file. When I copy this formula into another spreadsheet , it
retains the file reference thus making it useless.

Eg. When this formula - (='Sheet 1'!S1420) - is copied to another file
the formula changes as follows: ='[2006 Plan.xls]Sheet 1'!S1420, where
"2006 Plan.XLS" is the file name of the first file.

I need to copy this formula to over 60 files running Excel 2000 or
Excel 2003.

Please help me with this issue.

Thanks,

Jijy

--
jijy
------------------------------------------------------------------------
jijy's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=15042
View this thread:
http://www.excelforum.com/showthread.php?threadid=400340
Click to expand...
Click to expand...
 
Top