How to Force MS Excel to Always Reference the Same Cell

This article was previously published under Q51918
This article has been archived. It is offered "as is" and will no longer be updated.
  • Microsoft Excel for Windows 3.0|3.0
  • Microsoft Excel for Windows 4.0|4.0
  • Microsoft Excel for the Macintosh 3.0|3.0
  • Microsoft Excel for the Macintosh 4.0|4.0
  • Microsoft Excel for the Macintosh 5.0|5.0
SUMMARY
To continually reference the same cell in a formula in Microsoft Excel,use the INDIRECT function.
MORE INFORMATION
Absolute referencing references the same cell as long as no cells areadded or deleted in the column above the cell you are referencing. Thismeans that when you insert or delete a column so that the cell you arereferencing is moved, the formula references the new cell location.

For example, if you type the value 10 in cell A1 and refer to it in cellB1 with the formula "=$A$1" (without the quotation marks), the value 10appears in cell B1. If you then insert a cell over cell A1 so that thevalue 10 is moved to cell A2, the formula in cell B1 is "=$A$2."

To anchor the same reference while you insert or delete rows, columns, orcells, use the INDIRECT worksheet function. For example, if you want Excelto always reference cell A1, use the following formula:

   =INDIRECT("$A$1")				


Below is an example of a formula that would use the INDIRECT function toalways sum the first 10 rows of column A:

   =SUM(INDIRECT("$A$1:$A$10"))				
REFERENCES

Microsoft Excel 97

For more information about the INDIRECT function click the Index tab inMicrosoft Excel Help, type the following text

   indirect				


and then double-click the selected text to go to the "Indirect function"topic.

Earlier Versions

"Online Help," version 5.0

"Function Reference," version 4.0, page 238

"Function Reference," version 3.0, page 129

"Functions and Macros," version 2.x, page 61
sort lock XL
Properties

Article ID: 51918 - Last Review: 12/04/2015 09:03:54 - Revision: 1.5

Microsoft Excel 97 Standard Edition, Microsoft Excel 95 Standard Edition, Microsoft Excel 5.0 Standard Edition, Microsoft Excel 98 for Macintosh

  • kbnosurvey kbarchive KB51918
Feedback