Excel Defined Name

Microsoft Excel provides the capability to refer to a cell in a worksheet by the cell's row and column location, e.g, A6, or if the cell is in another worksheet within a workbook by using the format '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:

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.


  1. Defined Names
    Person Software Consulting, LLC - Your Source For Excel Development


TechRabbit ad 300x250 newegg.com

Justdeals Daily Electronics Deals1x1 px

Valid HTML 4.01 Transitional

Created: Sunday May 20, 2012