Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a workbook with a couple of sheets that I want to add to a new workbook. My challenge is that one of the sheets being copied to the new workbook is keeping a reference to the original workbook.

    As an example:
    Workbook A has two sheets; MySheet1 and MySheet2. In MySheet1 there is a formula in Cell A6 ie "=Sum(A1:A5"). MySheet2 has a formula referencing MySheet1 such as "=Sum(MySheet1!A6)+Sum("C5:C10").

    Here is the challenge: I can copy MySheet1 to the new workbook just fine (formulas are fine). When I copy MySheet2 to the new workbook, the formula in MySheet2 (now in the new workbook) references back to the original workbook. The formula now looks like: "=Sum([OrigWBName.xls]MySheet1!A6)+Sum("C5:C10").

    How could I copy the MySheet2 to the new workbook without referencing the OrigWBName in the formula?

    Thanks for your assistance,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should select Sheet1 and Sheet2 in Workkbook A, and copy them to the new workbook. If you copy them together, the formulas will point to the copied sheet in the new workbook.

    If you first copy Sheet1, then separately copy Sheet2, Excel has no way of knowing that you'd want to let the formulas refer to a sheet that is already present in the destination workbook, so they will still refer to the source workbook.

Posting Permissions

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