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 f**unction. 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.