A common thing to do in Excel is to use one sheet for collecting raw data, such as sales totals, and then to use another tab, or even a workbook to create summations or reports; the thing is though, doing so requires you to have to link cells between them. Fortunately, this is a lot easier than it sounds.
To see how it works, first create or open a sheet that has some data in it; here, we'll use the data sheet shown below:
Highlight and copy the cells you want to link, in this case, the YTD totals for each of the prior ten years.
Next, start a new Tab, by clicking one of the tabs at the bottom of the workbook.
Then, move over to the place in your new tab where you want to place your link, and highlight the same number of cells that you started with on the first tab:
Then right click in the highlighted area, you should see this PopUp menu:
Look where it says Paste Options: there are six icons to choose from, they represent, from left to right: Paste, Values, Formulas, Transpose, Formatting, and Paste Link.
To paste the data from your other tab, choose Paste Link.
Note: Excel 2010 unlike prior versions of Excel lets you see what will be pasted into your sheet when you hover over the options.
To see what happened when you pasted your link, move to one of the cells that was copied over and note what it says in the cell display box:
In the example, "Raw Sales Numbers" refers to the name of the original tab, while the exclamation point is known as a pointer, in this case because it's pointing to the cell to follow (P7). Thus, in this sheet, the contents of cell E5 in the new tab is whatever is in cell P7 on the Tab called Raw Sales Numbers.
The process for linking between Workbooks, is virtually identical; the exception being, instead of pasting to a new Tab, you instead paste to cells in a whole different spreadsheet.
And the address in one of the pasted cells looks like this:
In this case, the cell contents shows first that it's an external Excel sheet, then the path address of the file, the sheet name, the tab name, and finally a range name that was created automatically for the range of cells that was specified.
Linking between tabs or workbooks is a useful thing to do when you want to have raw data in once place, and results in another; this is particularly true of large sheets where it can become difficult to see what is what; grouping results on a new tab helps to clarify your spreadsheets and makes them far easier to read and understand.
Copyright © 2007
Online Tech Tips.
Aseem Kishore (digitalfingerprint: a59a56dce36427d83e23b501579944fcakmk1980 (74.125.94.82) )Post from:
Link Cells Between Tabs and Workbooks and In Excel 2010
9:15 PM
