Join Dennis Taylor for an in-depth discussion in this video Creating linkage formulas, part of Excel 2007: Managing Multiple Worksheets and Workbooks.
If you need information in one worksheet that already exists in another worksheet, you could use the Copy/Paste method and link the two. But even better is a Linkage formula. Let's take a look at both methods here. In this workbook, there are four sheets that have similar kinds of data for different regions. A fifth sheet has been setup called RegionalSummary. You'll notice on each of these sheets too that cell G7 has been highlighted. It represents the total of the data for that particular region. Our RegionalSummary sheet is going to be gathering data from there.
There are two approaches to doing this. The standard or the lengthier approach is to start at the source data, for example East, and copy the item in question. So I'm in cell G7 here, right-click, Copy, then go to the destination, RegionalSummary sheet. Click here, right-click, Paste Special > Paste Link, click OK. 18,040. You'll notice also here as I double-click it, it does have absolute addressing. That makes no difference in this case.
Here and there it could, if you're tying to copy these. But we did. You see how the information is displayed there. I'm going to delete this and show you another method. This method starts at this destination, right here. We want to gather data from the other source. Equal, then click on the other sheet, click the cell in question, Enter. Same answer. The formula doesn't contain the dollar sign and again it usually doesn't make any difference. Notice that it does pick up the sheet name automatically and an exclamation point to signify that it is a sheet name.
Now sometimes the question comes up, could you type this? Well, surely you could but typing is an opportunity for making a typing mistake. The clicking method is very fast and we're going repeat this with Midwest. Equal, click on Midwest, click the cell in question, press Enter. You got your data. It could hardly be much faster. One minor point here and it doesn't make any difference here. If sheet names have a space in them you'll notice a slight difference. Now I wouldn't normally spell Midwest with a space between Mid and West but I'll do it for the example here.
I'm double-clicking on Midwest sheet. I'm going to put a space between the D and W and press Enter. What happened to this formula here? The single quotes are there. Now no damage, nothing wrong here, but it does make certain formulas look a little more complex. Again, it's a reminder you probably don't want to be typing formulas like this. Just click on the appropriate locations. That's just fine. I'm going to go back and change that because I don't usually do that anyway with Midwest. So I'll take that out and the formula of course will adjust automatically.
We're back to there. So you can see how easy this is to write formulas that gather data from different locations. Simply by clicking equal, clicking on the other sheet, clicking on the cell in question and pressing Enter. Linkage formulas let you quickly gather data from one and more worksheet of the same workbook.
- Navigating across worksheets and workbooks
- Displaying multiple worksheets or workbooks simultaneously
- Inserting, deleting, moving, and copying sheets
- Changing sheet names and tab colors
- Showing and hiding worksheets
- Creating linkage formulas among workbooks
- Locating and managing links