How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask saaga Your Own Question
saaga
saaga,
Category: Microsoft Office
Satisfied Customers: 756
24317596
Type Your Microsoft Office Question Here...
saaga is online now
A new question is answered every 9 seconds

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.
Relist: Answer quality.
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?
saaga and 2 other Microsoft Office Specialists are ready to help you
Expert:  saaga replied 5 years ago.
Hi there,

I assuming that this solution worked for you then? Thank you for the Accept and the generous Bonus!!

Related Microsoft Office Questions