Cell range not automatically updating after inserting or deleting rows?

Alok_Sharma · October 24, 2021, 10:29am

Version: 7.2.1.2 (x64) / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-IN (en_IN); UI: en-GB
Calc: CL

I maintain a travel long of every road trip in a spreadsheet. I then duplicate (copy) the previous spreadsheet that best matches the last road trip, since most of the entries are the same.

In this case, I have a duplicated a spreadsheet with 23 rows. The top most row is the header row and the bottom most row is the totalling row.

Formulas in the totalling row:
Cell B23 → =((A22+B22)-(A2+B2))
Cell C23 → =C22-C2

Now, in this road trip, I have deleted the rows from 19 to 22.

The problem is that whenever I insert or delete any rows, the formulas in the totalling row do not automatically get updated. I then manually have to correct each and every formula by updating the cell range.

In this case, I have edited the above formulas to:

Cell B19 → =((A18+B18)-(A2+B2))
Cell C19 → =C18-C2

I believe this problem has been carried forwarded while using MS Office. Earlier, it was working absolutely fine and then one day, the formulas stopped auto updating. And in the meantime, my Office 365 subscription expired, and I switched to LibreOffice, but the problem still persists.

I am unable to figure out if this problem is embedded in the spreadsheet or there something is wrong with the program settings.

karolus · October 24, 2021, 12:14pm

Hallo
It cannot work this way!

You should start in B23 and C23 with Formulas:

=(A22+B22)-(A$2+B$2)
=C22-C$2

instead deleting Rows 19 to 22, you should now copy and paste B23:C23 to B19:C19,
this way the formulas are correctly updated, and youre save on deleting the Rows 20 to 22.

Alok_Sharma · October 25, 2021, 9:33am

@karolus , thank you for the reply. I tried your solution and it worked.

However, I still remember that in Excel, whenever I used to insert or delete any rows, the formula used to automatically get updated.

I am confused, if isn’t this the default behaviour for all the spreadsheet programs.

karolus · October 25, 2021, 10:13am

I dont know the behaviour in Excel, but my guess: youre remember only the cases where you NOT delete the referenced Cells of some formula ?!

sokol92 · October 25, 2021, 11:01am

I did the steps in MS Excel 2016 that you indicated in the start message. Formula

 =((A22+B22)-(A2+B2))

after deleting lines 19-22 it gives an error. This is as it should be, since cells A22 and B22 have been deleted. Formulas behave differently (in Excel and Calc) that refer to a range of cells.

Alok_Sharma · October 25, 2021, 12:00pm

sokol92:

I did the steps in MS Excel 2016 that you indicated in the start message. Formula
 =((A22+B22)-(A2+B2))
after deleting lines 19-22 it gives an error. This is as it should be, since cells A22 and B22 have been deleted. Formulas behave differently (in Excel and Calc) that refer to a range of cells.

@karolus & @sokol92 - You both are correct.

I don’t have Excel any more, so I created a new sheet in Calc and manually entered all the data.

Now, when I deleted the rows from 10 through 14, there was no error and the cell range got updated. But as soon as I deleted row 2 or row 22, I got an error. Besides, when I inserted new rows, either above or below row 2 or row 22 the cell range again got updated.

I get it now that in order to avoid Calc throwing errors, I have to make sure that I do not delete row 2 and row 22. Any rows in between can be deleted.

I couldn’t have figured this out without both of yours help, thanks