1

I have a 80+ row excel spreadsheet that calculates hours worked each month. Whenever I delete a row, because no longer working here, all the formulas below the deleted row are corrupted in that they change only the row number within the formula.For instance the simple formula

=SUM(HC22:HN22)

will change to

=SUM(HC19:HN19).

This only happens when I delete the row, otherwise formulas stay good.

Any ideas?

12
  • 4
    Yes, formulas will change when deleting rows. In your example, changing from HC22 to HC19 would lead me to believe you deleted 3 rows. It's nice that excel does this automatically. If this causes problems, then reconsider how your data are set up. If you present a sample image of what you would like to happen, then people may have some helpful ideas.
    – Isolated
    Apr 7, 2021 at 19:17
  • Tip: always save a backup before making changes. Rule of thumb: Better to make a back up you never use, than to be looking for a backup you never made. Apr 7, 2021 at 20:24
  • Yes, in this case, more than 1 row was deleted, hence 22 to 19. Is there ANY way to avoid this in the future, or will I need to check EVERY formula each time a row or more is deleted???
    – Hensley
    Apr 7, 2021 at 21:17
  • Depending on how things are setup and which rows are getting deleted (in relation to the cells with the formulas and the cells being SUMmed), absolute cell references may help. Apr 7, 2021 at 21:24
  • And this only happens to rows BELOW the row I have deleted. So if I have to delete a row, say #3, I will have to change all the formulas in each column where there is a formula. In my case, 6 columns. So today, 492 formulas!! Arggghhhh!!!!
    – Hensley
    Apr 7, 2021 at 21:31

4 Answers 4

0

I suspect the following problem. Your formula in say row 22, contains references to cells in row 21 (or ranges inclusive of row 21). If you delete row 21, this will result in #REF! errors (or shrink the inclusive range by 1 row). What I guess you want is for the formula to update to always refer to the row above it (not hard-coded to a row number) or for the range height to stay the same (i.e. always sum five rows above).

To do this, you can use the OFFSET function. Lets say in A22 you put:

=OFFSET(A22,-1,0)

This looks one row above A22 and will return the value in cell A21. However if you delete row 21, the old row 22 will become the new row 21 and update its own formula automatically to =OFFSET(A21,-1,0) which will now return the value in A20.

OFFSET can also return a range (i.e. five cells above it, in one column) like this:

=SUM(OFFSET(A22,-5,0,5,1))

CAVEAT: OFFSET is a volatile function and will slow down large workbooks considerably. In such a case, you can use a "relative named range" e.g. called "CellAbove". Learn how to define such a name here: https://exceljet.net/named-ranges (see the section called Named ranges can also be relative although the whole article is excellent at explaining everything you might need to know about named cells and ranges).

0

Better late than never; I've run through the same issue and kind of solved it like this:

I've created a new sheet on excel and use it as source for my formulas. It's a copy from the original sheet.

After that, created a vba macro which blanks all cells on this souce sheet and then paste the updated values from the original sheet.

If I delete a row from the original sheet, it wont affect formulas as they are using the "source" sheet.

Of course, you have to run the "blank & copy/update" macro each time you want changes to take effect. This will copy all values from original sheet into source sheet and your formulas will take it from there without their ranges being modified.

Hope it helps someone on future.

0

I just replaced the references with the indirect function. Instead of doing =SUM(A22:A44), you can do =SUM(indirect("A22:A44")). This will not be changed when deleting rows.

2
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Aug 19, 2022 at 18:12
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Aug 19, 2022 at 21:47
-2

The correct way to make formulas not-relative is to add a $ sign in front of the dimension you want fixed.
While creating a formula, you can press F4, and it automatically adds the $ signs; if you want to add it later, just type it into the formula, in your example: =SUM($HC$22:$HN$22). If you want only the rows to stay constant, use =SUM(HC$22:HN$22) - if you want only the columns fixed, use =SUM($HC22:$HN22), etc.

3
  • Nice!! I thinkI understand what you are saying and will give this a try.
    – Hensley
    Apr 7, 2021 at 21:39
  • 1
    That formula will still adjust if whole rows are deleted above row 22.
    – teylyn
    Apr 7, 2021 at 21:40
  • Yep, as mentioned above. I tested this and it still adjusts. Apr 7, 2021 at 21:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .