How to force Excel to always reference the same cell (214143)

SUMMARY

This step-by-step article shows you how to use the INDIRECT function to continually reference the same cell in a formula in Microsoft Excel 2000.

back to the top

How to Use the Indirect Function

To anchor a reference while you insert or delete rows, columns, or cells, use the INDIRECT worksheet function. For example, if you want Excel to always reference cell A1, use the following formula: To use the INDIRECT worksheet function, follow these steps:

1. Start Excel, and then open a new workbook.
2. Create the following worksheet:

   A1: 2
   A2: 3
   A3: 4
   A4: 5
   A5: 6
   A6: 7
   A7: 8
   A8: 9
   A9: 10
   A10: 11

3. Type the following formula in cell B1:

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

   The formula returns 65.
4. Select cell A7.
5. On the Insert menu, click Rows.
   
   The formula in cell B1 now returns 54, and the cell references are unchanged.

back to the top

Problems with Absolute References

This section explains why absolute references may be less useful than references that you make with the INDIRECT function.

Absolute referencing in Excel references the same cell or cells as long as no cells are added or deleted in the column above the cell or cells that you are referencing. This means that when you insert or delete a row or column so that the cell that you are referencing 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 cell B1 with the formula the value 10 appears in cell B1. If you then insert a row over cell A1 so that the value 10 is moved to cell A2, the formula in cell B1 changes to the following: back to the top