excel reference locked to original wookbook

Posted on 2012-08-21
Medium Priority
Last Modified: 2012-08-25

I have a dashboard that is pulling in report data from 6 other sheets in the workbook, to create graphs (charts) and other reference information.

My problem is that I want to use the dashboard sheet as a template and be able to copy it into other excel files that contain the same layout (but different data).  When I copy the sheet across to a new file all the formulas are still referencing the original workbook and not the new one I have pasted it in to, even though the tabs are named the same way.  If I close the original workbook the formula has a file reference in it.

How can I copy the sheet across without excel automatically updating the formulas to reference the old file?

Question by:eezar21
LVL 34

Accepted Solution

Rob Henson earned 2000 total points
ID: 38316924
Unfortunately, when copying the sheet it will always copy the reference to another sheet unless the source sheet is copied at the same time.

The way round it is to use the Links window.

Once the new file is saved open the Links window (Excel 2003 - Edit > Links, not sure with Excel 2007 & later). This will show the list of links which will include the old file. Select the old file and click on the change source button. In the browser window that comes up browse to and select the new file and click OK thus changing the link to the new file.

This may not chnage the data links within the charts though. Might be worth referencing the data for the charts onto the same sheet as the charts, hidden columns or rows or out of sight somewhere. Then when the charts are copied to a new sheet, the source data is the same sheet so will be changed suitably with the change to links.

Rob H

Author Closing Comment

ID: 38331945
Perfect, that worked a treat!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question