Suppose you have a simple formula “=A1+1000” in cell A2 that references the cell above it, A1. If you insert a new row above row 2, the formula in A2 will not automatically update to reference the new cell A2, which still references to A1.
Before inserting a new row:
After inserting a new row:
Similarly, if you delete row 2, the formula in A3 will not automatically update to reference the cell above it, which is now A1. And #REF displays because B2=B2+1000 is impossible.
Before deleting Row 2:
After deleting Row2:
One way to avoid the above two problems and always get the value from above cell when inserting or deleting rows in Excel is to use a simple formula with Excel INDIRECT function and Excel ADDRESS function. This post introduces these two Excel functions and provides a formula to get the cell value above the selected cell when inserting or deleting rows.
EXCEL INDIRECT Function and ADDRESS Function
The Excel INDIRECT function is used to return a reference to a cell or range of cells based on a text string that represents the cell reference. This allows you to create dynamic formulas and references that change based on the contents of other cells or the result of other calculations.
The Excel ADDRESS function is used to return the cell reference as a text string based on a specified row and column number.
To combine the bove two Excel functions, we can get a dynamic cell reference in the formula which can help us always get the cell above the selected cell. In order to achieve it in this worksheet, we can use the formula “=INDIRECT(ADDRESS(ROW()-1,COLUMN())“, instead of using the reference address of the cell, such as “B1” to refer to.
Steps of Creating the Formula with INDIRECT Function and ADDRESS Function
Step1: Enter the following formula into Cell B2:
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1000"
“=INDIRECT(ADDRESS(ROW()-1,COLUMN()))” replaces the original data that is the cell reference.
Step2: Drag down the formula to copy it to B3. The formula “=B2+1” is replaced by the new formula “=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1000” in B3, the result is the same.
Step3: When inserting a new row above the selected cell, the formula will be recalculated and the nested function ADDRESS(ROW()-1,COLUMN()) always indicates to the cell above the selected cell.
Note that if you have new data in new row above the cell when inserting a row, the formula will always points to the new position just above the current cell.
