• 100% Satisfaction Guarantee
saaga,
Category: Microsoft Office
Satisfied Customers: 757
24317596
Type Your Microsoft Office Question Here...
saaga is online now

# How to automatically adjust cells in a formula when referencing

### Resolved Question:

How to automatically adjust cells in a formula when referencing a cell that gets deleted,
i.e. =(\$G\$21-\$E\$20).
When I delete row 20 I want row 21 to change from =(\$G\$22-\$E\$21) to =(\$G\$21-\$E\$20).
Submitted: 5 years ago.
Category: Microsoft Office
Expert:  saaga replied 5 years ago.
Hello,

When you delete Row 20, Excel will automatically compensate by changing
=\$G\$22-\$E\$21
to
=\$G\$21-\$E\$20.

You shouldn't need to do a thing.
Customer: replied 5 years ago.
That is what I tried and it didn't work. I gor #REF.
Customer: replied 5 years ago.
In the new cell G21 it says #REF! whch was previusly G22 on row 22 before I deleted row 21.
Expert:  saaga replied 5 years ago.
If you delete Row 21, the \$E\$21 portion of the =\$G\$22-\$E\$21 formula will fail and give you the #REF error.
Customer: replied 5 years ago.

I already new that. I want the solution to the problem.

There should be a way to adjust the cell formula addresses automatically when ithe row changes up or down.

Expert:  saaga replied 5 years ago.
Which cell is =(\$G\$22-\$E\$21) in?
Customer: replied 5 years ago.

row 21 cell G21 was =(\$G\$20-\$E\$21 row 22 cell G22 was =(\$G\$21-E\$22)

I deleted row 21

row 22 became row 21 and cell G21 became #REF!

Customer: replied 5 years ago.
Expert:  saaga replied 5 years ago.
It looks like Column G, then, is a running total. Is that correct? And you want the cells in Column G to continue the running total even when you delete a row?
Customer: replied 5 years ago.

It calculating a new total based on the previous row G cell plus or minus the value in the current row E cell.

Expert:  saaga replied 5 years ago.
And you want the adjustment to be automatic? I ask because, if you remove the absolute (\$) references from the cells in column G, you can simply drag the first good (non-#REF) cell down over the #REF cells to correct them.
Customer: replied 5 years ago.
Thanks. I'll give it a try and get back to you.
Customer: replied 5 years ago.
That did not work.
Expert:  saaga replied 5 years ago.
You removed the absolute (\$) references from the formulas in the column G cells first? Then, you deleted row 21? Then, you selected cell G20, and clicked and dragged the fill handle in the corner down over the #REF cells to correct them?