Microsoft Office Tutorials and References
In Depth Information
Working with Multiple Worksheets
Displaying Data Stored in
You can reference data stored in another
workbook when needed, and display that data in the
current workbook. Typically, this is done for
convenience, so you do not need to open that other
workbook in order to look up related
information. For example, you might want to reference
the product codes in an inventory workbook, for
use in your marketing analysis workbook.
You can also reference the data in that other
workbook for use in a formula. For example, perhaps
you want to reference the current inventory
total for a product, for use in a sales
projectionplanning-budget worksheet. Follow these steps:
Reference a cell in another workbook when needed.
5. Press Enter. The contents of the referenced
cell appear in the result cell you selected in
1. Open both workbooks—the workbook in
which you want the referenced data to appear,
and the workbook that contains that data.
After you create a reference to data in another
workbook, whenever you reopen the workbook
in which you created the reference, you will see
a warning just above the Formula bar, as shown
in Figure 6-16. The warning is telling you that
this workbook depends on data saved in another
workbook but nothing in this workbook has
been updated automatically. Because the data in
the other workbook might have changed, Excel
recommends that you click the Enable Content
button to update it. If you don’t want to update
the worksheet with data from the other
workbook (because it’s wrong, or you’re in a hurry, or
whatever), just ignore the warning and simply
continue working. You can close the warning bar
by clicking its Close button (the X) over there on
the right if you want.
2. Click the cell where you want the data to
3. Type the equals (=) sign.
4. Change to the workbook that contains the
data you want to reference. Change to the
worksheet that contains the data by clicking
that worksheet’s tab. Then scroll if necessary
so you can see the cell containing the data,
and click it.
If you want to type the cell’s address yourself,
you need to follow the rules: first type the
name of the workbook in square brackets (as
in [HHQtr1Breakdown.xlsx]), followed by the
name of the worksheet, followed by an
exclamation point (!), followed by the cell address
—for example, type =[HHQtr1Breakdown.
xlsx]January!K8. (See Figure 6-15.)
Update the data referenced in another
workbook when needed.