Linking Workbooks

I have 2 files:  2012 Sales Detail: 

And Sales Summary: 

I want the grand total from the 2012 Sales Detail file in cell N8 to populate cell B2 from the Sales Summary file, but have it be linked so that any changes in the 2012 Sales Detail workbook will be reflected in the Sales Summary workbook.

There are two easy ways to do this.  In both methods, both workbooks need to be open: 

1) In cell B2 of the Sales Summary workbook, enter an equal sign =. then click on cell N8 of the 2012 Sales Detail workbook, and hit enter.  Cell B2 should now contain the following:

You will see the link in the formula bar. The brackets [ ] contain the linked workbook name, after that the worksheet name is identified followed by an !, then the cell reference with absolute value $’s. 

2) The alternate way is to right click on cell N8 of the 2012 Sales Detail workbook and copy that cell.  Then, in cell B2 of the Sales Summary workbook, right click and select Paste Special->Paste Link: 

The result will be the exact same entry as the previous method: 

Now, if a change is made to the sales figures on the detail workbook, the total will automatically update on the summary workbook:

Now, let’s say you make a change to the detail workbook, save and close it, and the summary workbook is not open: 

Here is what will occur when you open the summary workbook:

Notice several things:
1) the sales have not updated
2) the link in the formula bar now indicates the location of the workbook
3) a warning indicating that the automatic updates are not enabled, and it gives you the option to enable that link

Click on the “Enable Content” option and the workbook will now be updated: 

Now, the next time you have the same series of events, Excel will give you the option to update the data:

I hope this is helpful is understanding how linking workbooks works in Excel.  If you have any questions or comments, drop me a line or list them in the comment section.

Happy Excelling!

 

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

0 Responses to Linking Workbooks

  1. Oz du Soleil August 7, 2013 at 11:50 pm #

    Can you say something about the pros and cons? I know that a lot of people link workbooks. When I was learning, however, my mentor warned don’t ever ever ever link workbooks. Of course, the concern is about links getting broken … emailing one workbook but not the other; or, moving one out of a folder but not the other.

    So, what would you offer as a good reason to link workbooks AND the precautions to take?

Leave a Reply