'worksheet_name'!cell_location. E.g., suppose I have a workbook
named Sales that has two worksheets for two salespersons. Suppose one worksheet
is named Jack and the other Jill (note: you can give worksheets names besides
the default "Sheet1", "Sheet2", etc. by right-clicking on the tab for the
worksheet and choosing Rename). I want to take the total sales amount
for Jack, which is in cell C21 in the worksheet Jack and add it to the total
sales amount for Jill, which in cell C21 in the worksheet Jill, and add them
together. I can use the forumla ='Jack'!C21+'Jill'!C21 to produce
a total for both. Note: you don't need to enclose the spreadsheet name in single
quotes if the spreadsheet name doesn't include spaces or special characters,
such as "-"; if the spredsheet name does include those then you will need to
enclose its name in single quotes. It doesn't hurt to enclose the spreadsheet
name in single quotes even in those cases, so you could make it a practice to
always use them.
If I inserted or deleted rows in either the Jack or Jill spreadsheets, the
cell location C21 would be updated appropriately in the forumla to reflect
the change. E.g, if I inserted a row before C21 in Jill, the forumla would
automatically be changed for the total for both to
='Jack'!C21+'Jill'!C22' to reflect that change.
However, I sometimes insert hyperlinks in cells to refer to a location in another worksheet in a workbook. E.g., suppose I had a worksheet that had the following information:
| A | B | |
|---|---|---|
| Salesperson | Total | |
| 1 | Jack | $5,202 |
| 2 | Jill | $7,405 |
I have a formula in cell B1 that is 'Jack'!C21 and one in
B2 that is 'Jill'!C21. I also have inserted a hyperlink
in cells B1 and B2 that allows me to click on the totals for each sales
person and be taken to the cell containing the total in the relevant
spreadsheet, i.e., either the spreadsheet "Jack" or "Jill". If I insert
a row in Jill before C21, the forumla will get updated appropriately, but
the hyperlink will not, at least as of Microsoft 2007. Note: you can
insert a hyperlink for a cell that points to a location in a
spreadsheet by clicking on Insert choosing Hyperlink,
then Place in This Document, then Cell Reference, then
picking the appropriate spreadsheet in the workbook and placing the
cell location, e.g. C21, in the "Type in the cell reference" field.
I can address the problem of the hyperlink location not being updated when
the spreadsheet is modified by using a defined name for the
hyperlink rather than referencing a specific cell in a specific spreadsheet.
To create a defined name for a cell, you can click on the cell to select
it, or click on a cell range, if you are associating the name with
a range of cells, then select Insert, Name, and
then Define in Excel 2000. For Excel 2007, after you've selected
the cell or range of cells, click on Insert, then Formulas,
then select Define Name in the Name Manager section.
For the "Names in workbook" field put the name you wish
to use. The "Refers to" field will automatically have the worksheet and
cell location you were on when you started defining the name, e.g.,
=Jack!$C$21 (note: a "$" is placed before the row and the
column). Click on OK when you have defined the
name. To use the defined name in another worksheet for a hyperlink,
select the cell for which you wish to insert the hyperlink by clicking on
the cell, then choose Insert, Hyperlink, Place in
This Document, then click on Defined Names to see the names
you have defined. Select the defined name you created and then click on
OK. Now, if you insert or delete rows before the relevant row
in the spredsheet to which the hyperlink points, the hyperlink will be updated
as well.
A Defined Name must begin with a letter or an underscore, "_" and consist of only letters, numbers, or underscores. Spaces are not permitted in a Defined Name. A Defined Name may not be the same as a valid cell reference, e.g., you can't use C21 as a Defined Name. A Defined Name is not case sensitive.
CAUTION: If you are working on a workbook that will be used in both Excel 2003 and Excel 2007, be aware that many Names that are valid in Excel 2003 are invalid in Excel 2007. This is due to the increased number of columns in Excel 2007. For example, in Excel 2003, the Name ABC1 is valid because it is not a cell reference. However, in Excel 2007, ABC1 is, in fact, a valid cell reference, so the Name is invalid.
Another advantage of using defined names is that it makes it easier to understand why a particular value is being used in a formula or hyperlink. E.g., Jill_Total has more meaning than C21 and TaxRate has more meaning than E2, if you had a sales tax rate stored in location E2.
Reference:
Created: Sunday May 20, 2012