excel reference locked to original wookbook

Posted on 2012-08-21
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 31

    Accepted Solution

    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

    Perfect, that worked a treat!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now