Microsoft KB Archive/214143

= How to force Excel to always reference the same cell =

Article ID: 214143

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214143



For a Microsoft Excel 98 and earlier version of this article, see 51918.

IN THIS TASK
SUMMARY
 * How to Use the INDIRECT Function
 * Problems with Absolute References

REFERENCES



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:

=INDIRECT("$A$1")

To use the INDIRECT worksheet function, follow these steps:  Start Excel, and then open a new workbook. Create the following worksheet:

A1: 2

A2: 3

A3: 4

A4: 5

A5: 6

A6: 7

A7: 8

A8: 9

A9: 10

A10: 11

 Type the following formula in cell B1:

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

The formula returns 65. Select cell A7. 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

=$A$1

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:

=$A$2

back to the top

