Links in a blink: Excel data can collaborate with data in other workbooks.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 bal·ly·hoo n. pl. bal·ly·hoos 1. Sensational or clamorous advertising or publicity. 2. Noisy shouting or uproar. tr.v. 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 work·book n. 1. A booklet containing problems and exercises that a student may work directly on the pages. 2. A manual containing operating instructions, as for an appliance or machine. 3. (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 See screen shot. 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 Operating Expense The essential things that a company must purchase in order to maintain business. Notes: For example, the payment of employees wages are an operating expense. Also known as OPEX. 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 exclamation point: see punctuation. exclamation point - exclamation mark 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 Cell notation in chemistry is a shorthand way of expressing a certain reaction in an electrochemical cell. The cell anode and cathode (half-cells) are separated by two bars or slashes, with the anode on the left and cathode on the right. ($D$9). Linked cells can be copied to other cells, but if you want the reference to be relative, the absolute notation notation: see arithmetic and musical notation. How a system of numbers, phrases, words or quantities is written or expressed. Positional notation is the location and value of digits in a numbering system, such as the decimal or binary system. ($) must be removed. To do that, press F2 (the edit key A key combination or function key that changes the program into edit mode when pressed. ) and then remove them either manually or by pressing F4. Links also can be included as part of a formula. For example, the following formula divides by 2 the sum of the linked amount plus the value in B 15 on the dependent workbook: =([SubsidiaryA.xls]Budget!$D$9+B15)/2 When the source file is open, only the above formula appears on the dependent workbook formula bar. But when the source file is closed, the displayed formula includes both the drive (F) and directory (Data) of the source file. So, applying the above example, this formula will appear: ='F:\Data\[SubsidiaryA.xls]Budget'!$D$9 Note the addition of two apostrophes in the formula; they're placed before and after the link location. BREAKING AND EDITING LINKS Now that we've created links, let's see Let's See was a Canadian television series broadcast on CBC Television between September 6, 1952 to July 4, 1953. The segment, which had a running time of 15 minutes, was a puppet show with a character named Uncle Chichimus (voice of John Conway), which presented each how they can be erased e·rase tr.v. e·rased, e·ras·ing, e·ras·es 1. a. To remove (something written, for example) by rubbing, wiping, or scraping. b. or changed. It's easy to change all links to a source workbook. Click on Edit, Link on the menu bar. Note: Be aware that the Link button will appear faded (which means it's not available) if the links in the workbook are connected only to worksheets of the same workbook (Excel file). For the Link function to be available, at least one link must be to a different workbook. You use a different process if you want to update only a specific link. To see how it works, save and close the Consolidating workbook and save the SubsidiaryA workbook as SubNew.xls. Then reopen re·o·pen tr. & intr.v. re·o·pened, re·o·pen·ing, re·o·pens 1. To open or be opened again: Officials reopened the airport after the snow was cleared. Schools reopen in September. the Consolidating workbook and when a message box appears asking whether you want Excel to Update Links, click on Yes. Links in the Consolidating workbook still will refer to SubsidaryA.xls. To change the link source to SubNew, click on Edit and the Links icon, which opens the Source File box. Click on SubsidiaryA.xls and on the Change Source button. Finally, in the File Directory box, click on SubNew.xls and on OK. Now all of the links are updated to the new source workbook. For example, B5 in the Consolidating workbook now has this formula: =[SubNew.xls]Budget!$D$5 This method works only if the old and new source files have the same worksheet name--in this case Budget. In Excel after 2001, delete files See file wipe and delete. another way Click on Edit and Links and select your workbook and click on the Break Link button. FINDING LINKS The process gets less friendly if you want to change selected links, because before you can change them, you've got to find them--and it's that difficulty that soured Excel links' reputation. However, there are small programs (called add-ins) that can make the job easier. For a list of several, see "Easy Breaks," at right. If for some reason you'd rather not download and install an add-in, there are several ways to track down links. One is to search for a specific character common to the link's formula, such as a bracket or an exclamation point. Start by evoking the Find and Replace function with Ctrl+E Click on the Find tab and in the Find What box, type a left-hand bracket (1) or an exclamation point (t) and click on the Find Next button. Excel then will find the first occurrence of the target character, giving you access not only to your target formula, but to any other formulas that inconveniently in·con·ven·ient adj. Not convenient, especially: a. Not accessible; hard to reach. b. Not suited to one's comfort, purpose, or needs: inconvenient to have no phone in the kitchen. happen to contain either of those two characters. As a further disincentive dis·in·cen·tive n. Something that prevents or discourages action; a deterrent. disincentive Noun something that discourages someone from behaving or acting in a particular way Noun 1. , this process must be repeated to find each linked cell. Another way is to command Excel to display every worksheet formula. To do that, press Girl+- (tilde A symbol used in Windows, starting with Windows 95, that maintains a short version of a long file or directory name for compatibility with Windows 3.1 and DOS. For example, the short version of a file named "Letter to Joe" would be LETTER~1. Then "Letter to Pat" becomes LETTER~2. ). Exhibit 6, below, shows a section of a worksheet with all its formulas displayed. [ILLUSTRATION 6 OMITTED] But when a worksheet is large and contains many complex formulas, it's difficult to track down a particular link. Once a linked cell is located, it can be edited or deleted by the methods described above. If you're using an Excel version prior to 2002, there are other equally less-than-convenient methods for deleting links. In one, copy an entire worksheet and, using the Paste Special function, paste it as Values (see screenshot below). [ILLUSTRATION OMITTED] Although this method removes all links quickly and easily, it also changes all formulas--not just the linked ones--to values. Another unfriendly option is to link the de pendent file to itself using Edit, Link. Although this method eliminates the links, it can cause circular references A circular reference, or sometimes known as a run-around by some people, is a series of references where the last object references the first, thus causing the whole series of references to be unusable. errors. A final option is to delete the source workbook or move it to a different drive or directory. Then, when you open the dependent workbook and Excel recognizes the absence of the source file and the links, it displays a message box that allows you to recreate the links using a different workbook. As you can see, although Excel links enhance your use of this spreadsheet tool, the challenging offset is the difficulty in editing or breaking links once they're created. However, all that can be overcome just by taking the time to install one of the add-ins listed in "Easy Breaks" below. Key to Instructions To help readers follow the instructions in this article, we use two different typefaces This is a list of typefaces. Serif Here you can find a graphical version of this table.
Sans serif Short horizontal lines added to the tops and bottoms of traditional typefaces, such as Times Roman. Contrast with sans-serif.  type Shows the names of files and the names of commands and instructions that users should type into the computer. Easy Breaks Excel add-ins speed the chore of finding and deleting Excel links. Microsoft, for example, offers a free "wizard" (Delinks.exe) for the job (http://support.microsoft.com/ support/kb/articles/Q188/4/49.ASP) that creates an option on the Tool menu that provides a step-by-step method for deleting links. Another free add-in is available at www.bmsltd.ie/MVP/ Default.him It, too, creates an additional option on the Tools menu, called Find Links (see screenshot below). [ILLUSTRATION OMITTED] Some link management add-ins are not free. Their advantage is that, along with a link searcher, they contain several Excel utilities. One, Macro Systems, can be downloaded at www.add-ins.com/ linkfind.htm for $49.95. Or a free, limited-time, shareware Software on the "honor system." The concept is that users try a product, and if they like it, they voluntarily pay a set registration fee or make a donation to the program's creator. There are tens of thousands of shareware programs; some fantastic, some awful. version of this add-in is available at www.sharewareorder. com/Link-Finder-for-Microsoft-Excel-download-8981.htm. You also can download JEM Plus from www.ozgrid.com/ Services/excel-find-locate-links.htm. It, too, sells for $49.95. Donald J. Reynolds, CPA (Computer Press Association, Landing, NJ) An earlier membership organization founded in 1983 that promoted excellence in computer journalism. Its annual awards honored outstanding examples in print, broadcast and electronic media. The CPA disbanded in 2000. , DBA, is a professor at Calvin College This article is about a liberal arts college in the United States. For the school in Switzerland, see Collège Calvin. For the U.S. president, see Calvin Coolidge. , Grand Rapids Grand Rapids, city (1990 pop. 189,126), seat of Kent co., SW central Mich., on the Grand River; inc. 1850. The second largest city in the state, it is a distribution, wholesale, and industrial center for an area that yields fruit, dairy products, farm produce, , Mich. His e-mail address See Internet address. e-mail address - electronic mail address is djr6@calvin.edu. 
Its good.but the problem is that you are saying to reffer exhibit 1,page 69 like that,but it is not showing here. |
|
||||||||||||||||
Printer friendly
Cite/link
Email
Feedback
Reader Opinion