Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to use Paste Formulas between Excel Sheet without any link

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 11-01-2012
Member
 
Join Date: Jan 2012
Posts: 17
How to use Paste Formulas between Excel Sheet without any link

I want to copy and paste several cells, each containing formulas, from one workbook to another. The formulas contain references to several different sheets and I want the paste to contain the formulas exactly as they are in the originating workbook. When I paste these cells into the destination workbook, however, the formulas have inserted in them links to the originating workbook file name. The only way I can see to solve this problem is to individually select each cell, copy its formula from the edit formula field and then paste it into the edit formula field in the destination workbook. There are a lot of cells to copy and paste, so doing this cell by cell is not very feasible. Does anyone have any suggestions? Is there an option in Excel to disable the workbook link appearing in the formulas in the target workbook?

Reply With Quote
  #2  
Old 11-01-2012
Member
 
Join Date: Jun 2011
Posts: 486
Re: How to use Paste Formulas between Excel Sheet without any link

Check my way around for it, first in the source sheet, select the sheet, do edit > replace and in find what put equal sign =, then replace with use for instance ^^ (something that you are sure is not in the worksheet), then copy everything to the new workbook, then in the new workbook reverse replacement with = replacing ^^, then finally reverse replacement in the source workbook.
Reply With Quote
  #3  
Old 11-01-2012
Member
 
Join Date: Jun 2011
Posts: 632
Re: How to use Paste Formulas between Excel Sheet without any link

I am using the below method. I select my range to copy edit|replace
with: = (equal sign)
with: $$$$$= (some unique string)
replace all
Now my formulas are just text. Then copy > paste
Then clean up both ranges:
select the range
edit|replace
what: $$$$$= (that same unique string)
with: = (equal sign)
replace all
Now my text strings are formulas again. (Make sure you have worksheets in that new workbook that match up--else you'll be dismissing lots of dialogs!). I'd fix one formula manually just to test.
Reply With Quote
  #4  
Old 11-01-2012
Member
 
Join Date: Jul 2011
Posts: 631
Re: How to use Paste Formulas between Excel Sheet without any link

Select the cell containing the formula to be copied. Check that the cell references used in the formula will produce the desired result. If required, change the type of cell reference, for example, if a formula is needed to always refer to a cell, then this reference should be changed to absolute or mixed reference. Open the Edit menu and choose the Copy option. Then select the cell or cells in which you want to copy the formula. Open the Edit menu again. To copy the formula click Paste format. To copy the formula only click Paste Special, and then click Formulas.
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to use Paste Formulas between Excel Sheet without any link"
Thread Thread Starter Forum Replies Last Post
I cannot do Copy and Paste or Paste Special between Excel Workbooks Acca-OR Windows Software 5 26-04-2012 04:16 AM
Paste Special function does not work when I copy formulas in Excel Tarani MS Office Support 2 24-02-2012 07:22 PM
How to use Mail Merge from one Excel Sheet to another Excel Sheet Abélard MS Office Support 2 23-02-2012 02:59 PM
How to do Copy/Paste on protected work sheet in Microsoft Excel Chini mao MS Office Support 1 14-01-2012 06:58 PM
How formulas are calculated in Excel? The Recruiter Windows Software 5 26-03-2010 03:39 AM


All times are GMT +5.5. The time now is 03:59 AM.