Links in a Blink: Excel Data Can Collaborate with Data in Other Workbooks
Donald J. Reynolds · Journal of accountancy online/Journal of accountancy · 2006
Just as you collaborate with colleagues by picking up the phone or stopping by their office, any cell in Excel can likewise collaborate with some of its colleagues--that is, cells in other workbooks. Read how you can create helpful and time-saving links between cells in various workbooks. What makes the Excel linking function extraordinarily convenient is that once you invest the time to create a connection, you never have to do it again: It functions instantly for the life of the file without further prompting. But while that's great most of the time, it's not so convenient when you want to change or break a link. This problem has given Excel links a less-than-favorable reputation, but this article will show how to overcome that problem, demonstrating that the link function deserves more respect. Despite all the ballyhoo about the wonders of Microsoft's upgrade to Vista, don't count on any solution to the link-breaking problem. While the new Excel will be flashier and have new functions, link improvement is not one of them. DOWN TO BASICS A link is simply a formula that creates a connection between a cell in one workbook (called the source cell) and a cell in another workbook (called a dependent cell). Once you create a link, the dependent cell will update whenever the source cell changes. Links are created in three ways: the formula method, the paste link method and the direct method. Suggestion: To see the process in action, and as an aid in understanding the steps, create two Excel files (called workbooks)--SubsidiaryA and Consolidating, as shown in exhibit 1, below Then change the name of the worksheet (or page name within the workbook) from Sheet/ to Budget in both workbooks. [ILLUSTRATION 1 OMITTED] FORMULA METHOD To link Sales (D5) in SubsidiaryA to Sales (BS) in Consolidating, enter an equal sign (=) in Consolidating B5; then go to D5 in SubsidiaryA and press Enter (see exhibit 1). Now any change in SubsidiaryA's D5 shows instantly in Consolidating's B5 (see exhibit 2, page 69). To see the formula that Excel automatically created, click on Consolidating's B5 and this will appear in the formula bar: =[SubsidiaryA.xls]Budget!$D$5 [ILLUSTRATION 2 OMITTED] Tip: With this method, a plus sign (+) or a minus sign (-) may be used instead of an equal sign (=) in the first step; Excel will interpret them as equal signs when it creates the formula. [ILLUSTRATION OMITTED] PASTE LINK METHOD Go to SubsidiaryA's D6 and click on Edit, Copy. Return to Consolidating's B6 and again click on Edit, but this time click on Paste Special and then on Paste Link (see screenshot below). [ILLUSTRATION OMITTED] Now, as shown in exhibit 3, above, SubsidiaryA's cost of sales in D6 is linked to Consolidating's B6 and the Consolidating workbook should show cost of sales for SubsidiaryA at $675 (exhibit 3). [ILLUSTRATION 3 OMITTED] DIRECT METHOD In this method you write the link formula and then enter it directly in the dependent workbook (exhibit 4, at right). So, if SubsidiaryA's total operating expense is $220, enter it in D9. Then type this formula in B8 of the Consolidating workbook: =[SubsidiaryA.xls]Budget!$D$9 [ILLUSTRATION 4 OMITTED] As you can see, the formula must include the source workbook (SubsidiaryA), the source worksheet name (Budget) and the linked cell (D9). Also, the format must include the brackets, exclamation point and dollar signs as shown in exhibit 5, at right. [ILLUSTRATION 5 OMITTED] When you use the first two methods, Excel automatically creates absolute cell references--as shown by the dollar signs in the linked cell notation ($D$9). Linked cells can be copied to other cells, but if you want the reference to be relative, the absolute notation ($) must be removed. To do that, press F2 (the edit key) and then remove them either manually or by pressing F4. …