Results 1 to 2 of 2
Thread: Sheet Copy and Formulas
2009-10-09, 09:06 #1
- Join Date
- Dec 2000
- California, USA
- 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,
2009-10-09, 09:30 #2
- Join Date
- Mar 2002
- 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.