Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Excel Workbook Formula Copy (Excel 2003)

    Hi,
    This is my first post here. I am in trouble. I have almost wasted 4 hours searching google for this but solution found yet.
    Problem : I want to copy formulas from one workbook to another workbook without copying the links of the workbook. Just want to copy the formulas only between the worbooks without reference to the original workbook. Please help me. Million Thanks. Shashank

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Excel Workbook Formula Copy (Excel 2003)

    Welcome to Woody's Lounge!

    If you copy cells with formulas that refer to other cells within the same worksheet to another workbook, the formulas in the copied cells will refer to the new workbook.
    If you have cells with formulas that refer to other worksheets, you should select all relevant worksheets and copy them as a group to another workbook. If you copy the sheets one by one, the formulas will refer to the sheets in the original workbook, which is not what you want.
    If you only want to copy a small number of formulas, you can click in the formula bar, select the entire formula and copy it (Ctrl+C), then paste into a cell in another workbook.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Excel Workbook Formula Copy (Excel 2003)

    In addition to what Hans said, if you use Windows Explorer to make a copy of the entire file, then the formulas should not have links to the old workbook. You can then delete anything that you did not want to copy. Which method you use depends on how much of the workbook you want to copy.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MS Excel Workbook Formula Copy (Excel 2003)

    Another option and admittedly not very elegant.

    If you don't have too many formulas to copy, you can hold down the Ctrl key while clicking on each of your formulas (the source); this will select your formulas.
    Then press Ctrl+H to bring up the Find & Replace dialog (see picture).
    In the Find What: space, put an equal sign " = " as pictured.
    In the Replace With: space, put " ZZ= ", again as pictured.
    At the bottom of the dialog, click Replace All.
    This just changed all of your selected formulas to text entries.

    Now you can copy and paste or drag and drop those formulas to your target workbook.
    To finish the move, you'll need to select the moved formulas (as above),
    Press Ctrl+H again and reverse the steps (replacing " ZZ= " with just " = ".
    Now your formulas are exactly as they were in the original workbook.

    In the steps above, <font color=red>ignore all "quote marks"</font color=red> . The target cells in your original formulas will be unchanged regardless of where you paste them. Make sure this is the result you want. If your original formulas pointed to another worksheet (example: <font color=blue>=Data1!C9+Data1!G9</font color=blue> ), then it will still do so when copied over. If your new workbook doesn't contain a sheet named " <font color=blue>Data1</font color=blue> ", then the formula will not work as desired.
    Attached Images Attached Images
    - Ricky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •