But there’s still another possibility: you can even reference cells in your formulas that come from
other workbooks, that is, completely different Excel files. It’s possible you’ll need to calculate some
bottom- l i n e total for sal e s or budg e t data assi g n e d to di ffe r e n t wor kbooks, an d hav e i t al l di sti l l e d i n to
just one workbook; and that sort of task is eminently doable once the relevant cells are referenced.
True, you’ll want to proceed with care here, because if you email someone such a workbook—one
containing cell references to data in another workbook—and you don’t send along the latter wor kbook
as well, your data will be missing something.
The way to go about referencing, or linking, data across workbooks is actually pretty easy, and
similar to the method we described above for referencing cells across worksheets in the same
workbook. Let’s try this:
1. Open two new wor kbooks, and save one as Link, the other as Link2.
2. In cell G13 in Link type 65. In cell I2 in Link2 type 17. Now we’re going to try
and add the two numbers (needless to say, you can add many more than two,
and you can link ranges as well this way).
3. Remaining in Link2, type =I2+ in cell A1.
4. Click on Link, and simply click cell G13. You’ll see (Figure 7–12):
Figure 7–12. Note the more elaborate cell reference, pointing to a different worksheet in a different
workbook
5. Then press Enter. The answer appears.
Note the syntax of the formula we’ve just written. Because we’re working with cells in two
different workbooks, Excel needs to specify two things: the workbook in which the linked cell(s) is
located, as well as the worksheet, too. The name in brackets—[link.xslx]—obviously points to the
workbook. Note as well that it’s the cell that isn’t in the same workbook as the formula that needs all
these specifications, and note also that it’s written with dollar signs, signifying an absolute reference.
T ha t’ s be ca use i f y ou copy the for mul a down a col umn , for e xa mpl e , Exce l a ssume s y ou wa n t a l l the
copied formulas to reference the same cell in that other workbook.
Keep in mind that if you currently have only the workbook open that contains the linked cell and
you change its data—and then later open the workbook containing the formula—the formula will
recalculate automatically. And by the same token, let’s say you change the data in the linked cell, close
it and save it, thus leaving neither workbook open. When you open the formula-bearing workbook, it
will likewise recalculate. (Note: If you move one of the workbooks to a different folder, the current link
will be severed and will have to be reinstituted, if that’s what you want, requiring a fairly messy repair
job. An Edit Link dialog box appears, asking you to supply the new location of the moved workbook.) In
sum, wor ki n g wi th ce l l r e fe r e n ce s can be a bi t di ce y , an d shoul d be use d fr ug al l y . Apar t fr om the
moving-folder issue, tracking the cell references that contribute to your results can be daunting,
particularly if you need to analyze a mistake in a formula
other workbooks, that is, completely different Excel files. It’s possible you’ll need to calculate some
bottom- l i n e total for sal e s or budg e t data assi g n e d to di ffe r e n t wor kbooks, an d hav e i t al l di sti l l e d i n to
just one workbook; and that sort of task is eminently doable once the relevant cells are referenced.
True, you’ll want to proceed with care here, because if you email someone such a workbook—one
containing cell references to data in another workbook—and you don’t send along the latter wor kbook
as well, your data will be missing something.
The way to go about referencing, or linking, data across workbooks is actually pretty easy, and
similar to the method we described above for referencing cells across worksheets in the same
workbook. Let’s try this:
1. Open two new wor kbooks, and save one as Link, the other as Link2.
2. In cell G13 in Link type 65. In cell I2 in Link2 type 17. Now we’re going to try
and add the two numbers (needless to say, you can add many more than two,
and you can link ranges as well this way).
3. Remaining in Link2, type =I2+ in cell A1.
4. Click on Link, and simply click cell G13. You’ll see (Figure 7–12):
workbook
5. Then press Enter. The answer appears.
Note the syntax of the formula we’ve just written. Because we’re working with cells in two
different workbooks, Excel needs to specify two things: the workbook in which the linked cell(s) is
located, as well as the worksheet, too. The name in brackets—[link.xslx]—obviously points to the
workbook. Note as well that it’s the cell that isn’t in the same workbook as the formula that needs all
these specifications, and note also that it’s written with dollar signs, signifying an absolute reference.
T ha t’ s be ca use i f y ou copy the for mul a down a col umn , for e xa mpl e , Exce l a ssume s y ou wa n t a l l the
copied formulas to reference the same cell in that other workbook.
Keep in mind that if you currently have only the workbook open that contains the linked cell and
you change its data—and then later open the workbook containing the formula—the formula will
recalculate automatically. And by the same token, let’s say you change the data in the linked cell, close
it and save it, thus leaving neither workbook open. When you open the formula-bearing workbook, it
will likewise recalculate. (Note: If you move one of the workbooks to a different folder, the current link
will be severed and will have to be reinstituted, if that’s what you want, requiring a fairly messy repair
job. An Edit Link dialog box appears, asking you to supply the new location of the moved workbook.) In
sum, wor ki n g wi th ce l l r e fe r e n ce s can be a bi t di ce y , an d shoul d be use d fr ug al l y . Apar t fr om the
moving-folder issue, tracking the cell references that contribute to your results can be daunting,
particularly if you need to analyze a mistake in a formula
Tidak ada komentar:
Posting Komentar