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.