Duplicate sheets missing rows issue

[arutkows]

Although I managed to solve my problem, the following maybe of interest to others:
MacMini M4, Sequoia 15.5
Libreoffice 25.2.4
I duplicated a sheet using the duplicate sheet option. The duplicated sheet is missing original rows 6 and 7. The screenshots are attached. The sheet contains some 200-300 rows altogether, these are the only missing rows.

Solution : I found that the columns with the names of the month contained a formula which indicated they came from another sheet (something like =anothersheet.cellref). When I changed this column to actual values (copy and paste special) the problem disappeared !. But why only these two rows ? All rows are defined the same way.
The original spreadsheet came from an openoffice document. There are no hidden rows and the delete duplication option is not enabled (there are no duplicates anyway).

Before

After

[EarnestAl]

It looks like the rows are there (the values match) but the Month Ending cells are incorrect. Maybe Edit > Links to External Cells needed updating. Or maybe A6 had a formula like =TODAY()

[arutkows]

Thanks for your comments. I hadn’t noticed that the amounts are still correct (but only in the first block of amount columns) so it looks like the sheet duplication affected other columns in a strange way. It has something to do with there being formulas in columns because the copy/paste special fixed the problem. But why these 2 rows only (all cells in the column contained the same formula) remains a mystery.

[shantanuo]

Can you attach a sample file?

[arutkows]

Thsnks for your interest. I attach a spreadsheet which also contains my comments. I suspect that there is a bug in Libreoffice since it specifies that duplicating a sheet produces an identical copy. Whether the issue ls with the file originally created in Openoffice or to the fact that it seems to be related to formulas which reference another sheet I’ll leave to the experts.

Allocated Pension.ods (29.9 KB)

[karolus]

Before you duplicate Sheet B, fix the relative Sheet-reference to absolute Sheet-references, eg:

='Sheet A'.A8   # relative
to
=$'Sheet A'.A8   # absolute

[LeroyG]

This is because Sheet A (and Sheet A_2) has “abr-08” in A8, and Sheet B has “abr-08” in A6, so Sheet B_2 copies from Sheet B.A8.

[arutkows]

In reply to post by karolus, yes, that’s certainly the solution, although I seem to remember that I read somewhere that the duplicate option (as opposed to copy sheet option) results in an identical sheet which is not the case, since the references change. Changing references to refer to values rather then cell references also works but is obviously not as general.

[LeroyG]

@arutkows

cell references

Cell references without $ before are relative, including sheet names.