Copy formula between workbooks

  • Thread starter Thread starter Ken Runge
  • Start date Start date
K

Ken Runge

I have a number of workbooks which are identical in form. Each uses
multiple sheets. If a make a formula change in one and then copy the
formula to a different workbook the new formula references the original
workbook and sheet. How can I turn off this "feature" so that the copied
formula will not make any reference to the workbook, only the sheet?
Thanks
 
Hi Ken,
To enter the same formula in the same cell in a number of worksheets:
1)Click on the sheet tab of the first worksheet, hold Shift and click on the
tab of the last worksheet. This is called 'grouping'
2)Type the formulas needed in each cell
3) Ungroup the sheets by right clicking the tab of any sheet and selecting
ungroup

Bernard
 
When I need to do this, I put a " ' " (single quote) in front of the formula
so Excel treats it as text, then I copy the text and paste it. Then I remove
the single quote
Mark
 
Thanks, while waiting for a reply I came up with the same answer as the only
possible work around. (I think I should just go back to using QPro, a much
better program!)
 
WorkBOOKS not workSHEETS, but thanks anyway.
Hi Ken,
To enter the same formula in the same cell in a number of worksheets:
1)Click on the sheet tab of the first worksheet, hold Shift and click on the
tab of the last worksheet. This is called 'grouping'
2)Type the formulas needed in each cell
3) Ungroup the sheets by right clicking the tab of any sheet and selecting
ungroup

Bernard
Click to expand...
 
as long as the sheet names and ranges are the same in each workbook, you can
use:

EDIT -> LINKS and then press the change source button and choose the current
workbook. I use this often and it beats making each formula text as it
performs the change to every cell with that link referenced.

HTH

ryanb..
 
You may want to avoid the single quote. It's impossible to do an Edit|replace
and get that single quote back out.

I just change all equal signs (=) to dollarsigns followed by an equal sign
($$$$=).

Then I can edit|replace to insert them and edit|replace to remove them.
 
That and word perfect...

Mark

Ken Runge said:
Thanks, while waiting for a reply I came up with the same answer as the only
possible work around. (I think I should just go back to using QPro, a much
better program!)
Click to expand...
 
Back
Top