Please attach a sample file.

Created attachment 185308 [details]
This is a requested spreadsheet containing the reported problem.

I have attached a sample spreadsheet that exhibits the reported problem.

(In reply to bwilderhoo from comment #0)
> Actual Results:
> Example of a single line in the new sheet that was incorrectly created a a
> merger of the two lines shown under Expected Results below:

I don't see why exactly you say the single line was _incorrectly_ created.

In the original worksheet, you have 2 lines, but the conditions, the ranges, and the corresponding styles are either equivalent or the same. As it is, you could have the two lines merged in the original too.

Now, I can understand the surprise, thinking that "whatever I do, I expect an exact copy", meaning that you expected no "automatic" merging, even when it was possible. I could even understand a hypothetical claim that you planned these lines to be separated for a reason (e.g. a potential future separated/individual edition of the respective conditions or the respective styles), and that you don't want this merging. What I don't get is the assertion that it is is "incorrect".

IOW, you might disagree with how the copying procedure is handled, but the result is not incorrect.

Am I missing something here?

(I'm leaving NEEDINFO for the report)

Hi,

Thanks for the feedback.  Here is what you are missing (and it is a easy thing to miss).  The condition for range B18:M18 is to check the cell value against the cell $Budget.$B23, whereas the condition for range B19:M19 is to check the cell value against the cell $Budget.$B24.  Basically, the first range is simply checking the recorded monthly cell phone expense against the cell phone budgeted amount and highlighting yellow if expense amount exceeds budgeted amount. Similarly, for the second range, but the check is against the budgeted electricity amount.

For merged line that covers both rows with the range of B18:M19 the condition is to check the cell value against the cell phone budgeted amount.  The electricity budgeted amount is lost in the merge.  In fact, because the conditions are checking against different budgeted amounts no merged single condition can be used to cover both of the ranges.  The two lines are mutually exclusive checks and ranges that need to remain as is, and not merged when copied to a new sheet.

Hope this helps.

Please confirm that you are seeing what I have described above.

Thanks for your help.

(In reply to bwilderhoo from comment #4)
> Please confirm that you are seeing what I have described above.

I still don't see it. I added values to both sheets (original and copy) in row 19, and I see the same expected result. I modify the value in row 24 of the Budget sheet, and I see the same results in both sheets, every time.

Please either attach an example, a file with specific values in "Budget" that will trigger the "incorrect" (conditional) format (IIUC, a different resulting format in the newly-created sheet than the resulting format in the original sheet). Or, alternatively (and preferably ATM), please simply write down at least one value and where exactly to introduce it (i.e. in which cell of which sheet in your attachment 185308 [details]) in order to trigger the unexpected resulting format.

(In reply to bwilderhoo from comment #4)
> Hi,
> 
> Thanks for the feedback.  Here is what you are missing (and it is a easy
> thing to miss).  The condition for range B18:M18 is to check the cell value
> against the cell $Budget.$B23, whereas the condition for range B19:M19 is to
> check the cell value against the cell $Budget.$B24.  Basically, the first
And it's what the new condition does.

>.....
> 
> For merged line that covers both rows with the range of B18:M19 the
> condition is to check the cell value against the cell phone budgeted amount.
> The electricity budgeted amount is lost in the merge.  In fact, because the
> conditions are checking against different budgeted amounts no merged single
> condition can be used to cover both of the ranges.  The two lines are

I think it is not understood how the condicional format works.
For a range, like B18:M19 it analyzes the conditions for every cell, varying accordingly the relative ranges in the conditions.
E.g. for B18 -> $Budget.$B23 is analyzed, for B19 -> $Budget.$B24 is it.

> mutually exclusive checks and ranges that need to remain as is, and not
> merged when copied to a new sheet.

If you want the ranges not to be merged, use an absolute reference also for the row $Budget.$B$23

This was done to reduce duplicated ranges with the same conditions.

Sorry, but I don't think we are on the same page on this one.   I fully understand how conditional formatting works... and the yyyy sheet correctly reflects the conditional formats as they need to be.  That is, for row 18 the cell value for each month is compared to the $budget.$B23, and for row 19 the cell value for each month is compared to $budget.$B24.  In short, there are two distinct rows cells requiring comparison against two distinct budget cells. 

In short, as a retired computer programmer myself with 40+ years of experience, I believe this remains a bug.  At a minimum, the move or copy sheet function should not modify the column and row numbers used in cell references regardless of whether these references are defined within the conditional formats or simply used in the cell value calculations, and regardless of whether or not the absolute or relative column and row references are used.  The only change that I would expect during a move or copy of a sheet is the relative sheet name should be changed.  That is, any sheet names references yyyy should be updated to the 2023.  This seems to be working as expected.

The reason that absolute row addresses can not be used is because the budget sheet can have additional rows added over time, and this fact requires the automatic update of all row reference is other sheets in order to prevent incorrect cell values from being used.  Note, for this particular budget spreadsheet definition, new expenses (i.e. - rows) can be added over time to the budget sheet but no new columns are allowed.  Therefore, the other sheets need to use relative row numbers in order to be kept in proper sync with existing values when new rows (i.e. - expenses) are added to the budget.

I have used this same kind of conditional formatting in Microsoft Excel and it has worked as I described above.

The simplest thing to do is whenever a sheet is copied then make an exact copy the entire sheet contents including conditional formatting, and only update the relative original sheet name references to use the new sheet name.  The extra step that is apparently being done to reduce the number of distinct ranges in the conditional formats is very complex logic and prone to difficulties such as described in this bug report.  I recommend the keep it simple approach, and just keep the conditional formats in the new sheet as they were in the original sheet-- with the one exception of updating the relative sheet name references as described above.  Note, the bug is in the merge of rows during the copy sheet function.  The original sheet works just fine with the existing 2 lines using relative row references.  New sheets created as copies from the original should work the same as the original--- but in this case they do not because of the bug.

Hope this helps to get on the same page.

@ bwilderhoo

Perhaps you don't realize that you are replying to a bug report, not to one specific person. You have 2 different persons replying in the bug report you opened. One closed the report, while the other gave you the benefit of the doubt. So, now that you re-opened the report, and with the additional detail I just wrote, please read carefully the complete reply before answering.

(In reply to bwilderhoo from comment #7)
> I have used this same kind of conditional formatting in Microsoft Excel and
> it has worked as I described above.


That means that you are assuming that the exact same syntax is used by LO too. This is an understandable assumption, but unfortunately it is not correct.

> 
> relative sheet name references as described above.  Note, the bug is in the
> merge of rows during the copy sheet function.  The original sheet works just
> fine with the existing 2 lines using relative row references.  New sheets
> created as copies from the original should work the same as the original---
> but in this case they do not because of the bug.


In comment 5 I asked you to supply a value and in which specific location you would introduce it in order to trigger the problem you see. If you think that more than one value is necessary, please describe them. Alternatively, please attach a new file with the problem already triggered.

To be clear, I have the same suspicion as m.a.riosv. Let's see if bwilderhoo can reply with a relevant sample value(s) or equivalent.

Created attachment 185339 [details]
PDF containing annotated screen shots and other info to further clarify the problem

Attached PDF containing screenshots and other information about the problem, and provides answer to the last question asked by support personnel.

Hope this info makes clear the problem as I see it--- and have tried to report it previously.

(In reply to bwilderhoo from comment #9)
> Attached PDF containing screenshots and other information about the problem,
> and provides answer to the last question asked by support personnel.

Hmm? Who is "support personnel"?

> 
> Hope this info makes clear the problem as I see it--- and have tried to
> report it previously.

Since you admit that there is no way to trigger a real problem in the result (i.e. in the cells themselves), you seem to be requesting not to modify the copy in comparison to the original (worksheet) in any way (at least regarding the conditional formats), even when the resulting actual behavior in the cell's format is the same. I admit users might have their valid reasons (and relevant workflow) for this request.

The alternative to that interpretation (IMHO) is that you are not yet understanding that LO Calc deals with the syntax for CF in a different way than other programs do. The result is the same, but the syntax is slightly different.

There is a PDF, "Conditional formatting guide" for Calc. Search for it. Whether it would help in this case, IDK (I haven't read it myself).

You can also try at ask.libreoffice.org

At this time I’ll leave the status of this report without further modification. Someone else might have a different interpretation, and/or reaction.

(In reply to ady from comment #11)
> (In reply to bwilderhoo from comment #9)
> > Attached PDF containing screenshots and other information about the problem,
> > and provides answer to the last question asked by support personnel.
> 
> Hmm? Who is "support personnel"?
> 
> > 
> > Hope this info makes clear the problem as I see it--- and have tried to
> > report it previously.
> 
> Since you admit that there is no way to trigger a real problem in the result
> (i.e. in the cells themselves), you seem to be requesting not to modify the
> copy in comparison to the original (worksheet) in any way (at least
> regarding the conditional formats), even when the resulting actual behavior
> in the cell's format is the same. I admit users might have their valid
> reasons (and relevant workflow) for this request.
> 
> The alternative to that interpretation (IMHO) is that you are not yet
> understanding that LO Calc deals with the syntax for CF in a different way
> than other programs do. The result is the same, but the syntax is slightly
> different.
> 
> There is a PDF, "Conditional formatting guide" for Calc. Search for it.
> Whether it would help in this case, IDK (I haven't read it myself).
> 
> You can also try at ask.libreoffice.org
> 
> At this time I’ll leave the status of this report without further
> modification. Someone else might have a different interpretation, and/or
> reaction.

Thanks for your additional insights and the recommendation that I search for the CF guide for Calc.  I downloaded and read this brief 25 page document and it confirms my understanding of how CF are intended to work.  In fact, the following info from the document, if correct, confirms my hunch about how CF are defined for for a given sheet:

Use “Manage Conditional Formatting” dialog.
Select Format > Conditional > Manage menu A dialog box opens.
This dialog has a list of all cell ranges with conditional formatting on current sheet.

The problem I reported in this bug is that after copying an existing sheet, yyyy, to a new sheet 2023, the resulting "list of all cell ranges with conditional formatting on current sheet" was not even close to being logically equivalent to "list of all cell ranges with conditional formatting on the source sheet".   The sample data I provided was a very small set to clearly show the differences and loss of vital info (i.e. - cell references dropped and distinct and mutually exclusive cell ranges merged).  The original source spreadsheet from which this sample data was obtained, the Manage Conditional Formatting dialog for the source sheet yyyy listed 30 distinct mutually exclusive cell ranges with conditional formatting, and the 2023 sheet that was copied from this source sheet, only had 14 mutually exclusive cell ranges with conditional formatting.  The reduction that occurred during the sheet copy seemed to simply combine cell ranges using first range defined CF, and ignored the CFs for subsequent ranges being combined.  In the provided sample data you see that there is no references to $Budget.$B24 in the single combined cell range CF.  This info has been lost in the Manage Conditional Formatting dialog.

Anyway, thanks for your "support" in trying to understand and help the issue I have reported.  I will take a look at ask.libreoffice.org as you suggested.

(In reply to bwilderhoo from comment #12)
> .......  In the provided sample data you see that there is no references
> to $Budget.$B24 in the single combined cell range CF.  This info has been
> lost in the Manage Conditional Formatting dialog.
> .....

I explain to you that in comment#6.

(In reply to m.a.riosv from comment #13)
> (In reply to bwilderhoo from comment #12)
> > .......  In the provided sample data you see that there is no references
> > to $Budget.$B24 in the single combined cell range CF.  This info has been
> > lost in the Manage Conditional Formatting dialog.
> > .....
> 
> I explain to you that in comment#6.

Added new expense to the budget in between cell phone and electricity to test theory that missing $Budget.$B24 from the Manage Conditional Format is not a problem for proper conditional formatting of the monthly electric charges in new sheet 2023, as implied in comment 6.  Notice in this slightly modified spreadsheet that Feb electricity is incorrectly highlighted as yellow in the new 2023 sheet, but not in the original yyyy spreadsheet.   And reviewing the Manage Conditional Formatting dialog for both sheets shows that:

1. The condition for range B19:M19 was correctly updated in the yyyy sheet to compare cell value against $Budget.$B25, rather than against the prior value in $Budget.$B24.  
2. Whereas, there is no visible change to the single merged condition shown in sheet 2023 Managed Conditional Formatting dialog.

In short, the two sheets, one being a full copy of the other, behave differently for conditional formatting.  In sheet 2023, which has the merged single conditional format, $Budget.$B24 is treated as $Budget.$B{23+1}, whereas in yyyy $Budget.$B24 is treated as $Budget.$B24.  Sheet 2023 does not allow for relative row addressing to be used within a merged range.  This is a bug... plain and simple.  The correct logic for merging of conditional formats during the copy sheet function is to ensure that only absolute addressing is used in all of the conditions for adjacent ranges that might be merged.  If any relative addressing is found, then skip merging for that set of adjacent ranges.  I know this would mean a lot less merging would be done, but it would also ensure that sheets copied from other sheets that use relative addressing have logically equivalent conditional formatting that continue to work as intended even when rows are added or deleted resulting in relative address updates.  

I have reviewed ask.libreoffice.org as well as other bugs that folks have opened related to conditional formatting problems... and my view of this, which seems similar to those echoed by others in some of the bugs, is that conditional formatting has some serious hurdles to overcome.  One individual in a bug report indicated that the issue(s) may require structural code changes.  I don't know about that, but I do know that inconsistencies in behavior that folks are reporting, similar to what I am reporting in this bug, give at least the appearance of some significant usage model holes.   I can imagine many other problems that might be solved by restricting merging to those adjacent ranges that only use absolute addressing throughout.

Sorry about rambling on... but thanks for the lively discussions on this matter. Hopefully the info exchange can facilitate resolving some of the outstanding issues folks are having with conditional formatting.

(In reply to bwilderhoo from comment #14)
> (In reply to m.a.riosv from comment #13)
> > (In reply to bwilderhoo from comment #12)
> > > .......  In the provided sample data you see that there is no references
> > > to $Budget.$B24 in the single combined cell range CF.  This info has been
> > > lost in the Manage Conditional Formatting dialog.
> > > .....
> > 
> > I explain to you that in comment#6.
> 
> Added new expense to the budget in between cell phone and electricity to
> test theory that missing $Budget.$B24 from the Manage Conditional Format is
> not a problem for proper conditional formatting of the monthly electric
> charges in new sheet 2023, as implied in comment 6.  Notice in this slightly
> modified spreadsheet that Feb electricity is incorrectly highlighted as
> yellow in the new 2023 sheet, but not in the original yyyy spreadsheet.  
> And reviewing the Manage Conditional Formatting dialog for both sheets shows
> that:
> 
> 1. The condition for range B19:M19 was correctly updated in the yyyy sheet
> to compare cell value against $Budget.$B25, rather than against the prior
> value in $Budget.$B24.  
> 2. Whereas, there is no visible change to the single merged condition shown
> in sheet 2023 Managed Conditional Formatting dialog.
> 
> In short, the two sheets, one being a full copy of the other, behave
> differently for conditional formatting.  In sheet 2023, which has the merged
> single conditional format, $Budget.$B24 is treated as $Budget.$B{23+1},
> whereas in yyyy $Budget.$B24 is treated as $Budget.$B24.  Sheet 2023 does
> not allow for relative row addressing to be used within a merged range. 
> This is a bug... plain and simple.  The correct logic for merging of
> conditional formats during the copy sheet function is to ensure that only
> absolute addressing is used in all of the conditions for adjacent ranges
> that might be merged.  If any relative addressing is found, then skip
> merging for that set of adjacent ranges.  I know this would mean a lot less
> merging would be done, but it would also ensure that sheets copied from
> other sheets that use relative addressing have logically equivalent
> conditional formatting that continue to work as intended even when rows are
> added or deleted resulting in relative address updates.  
> 
> I have reviewed ask.libreoffice.org as well as other bugs that folks have
> opened related to conditional formatting problems... and my view of this,
> which seems similar to those echoed by others in some of the bugs, is that
> conditional formatting has some serious hurdles to overcome.  One individual
> in a bug report indicated that the issue(s) may require structural code
> changes.  I don't know about that, but I do know that inconsistencies in
> behavior that folks are reporting, similar to what I am reporting in this
> bug, give at least the appearance of some significant usage model holes.   I
> can imagine many other problems that might be solved by restricting merging
> to those adjacent ranges that only use absolute addressing throughout.
> 
> Sorry about rambling on... but thanks for the lively discussions on this
> matter. Hopefully the info exchange can facilitate resolving some of the
> outstanding issues folks are having with conditional formatting.

NOTE: For some reason, I am unable to attach the slightly modified spreadsheet.  But if you have downloaded the originally attached spreadsheet and want to recreate the results I cover in this comment, simply go to the Budget sheet and insert row above Electricity row and add Car expense for $50.00.  Then review the other two sheets as described in the comment.

(In reply to bwilderhoo from comment #14)
> ...
I don't know why you can't attach a new file. Go up and under the attachments there is a link
'Add an attachment (proposed patch, testcase, etc.)' 
to do it with your comment.

OTOH, please detail better the steps to modify the file.
1) Insert a row above Budget.19, with 'Car expenses' '$50'.
This affects nothing, what else to do?

Created attachment 185365 [details]
Slightly modification of previously provided sample data

Third attempt at attaching an updated sample data file.

Sorry, but it's becoming a bit boring, deal with your question.

On the last sample '2023' it's not a copy of 'yyyy', I did a copy of 'yyyy' and it works fine, it doesn't merge the conditions because in 'yyyy' the comparison now is with B23 and B25, so when copying the sheet conditions are not merged.

Finally!

Steps to reproduce:
1. Open attachment 185308 [details]
2. On the _3rd_ worksheet, named "yyyy", cell C19, enter the number: 119 [ENTER]
3. Duplicate Worksheet "yyyy".
4. Worksheet "Budget", select entire row 24, [CTRL]+[+]
5. Compare conditional format of $yyyy_2.C19 vs $yyyy.C19

Note that the CF for cell $yyyy.C19 refers to "$Budget" (as an _absolute_ reference to the worksheet). Hence, both worksheets, the original "yyyy" and the copy "yyyy_2", contain CF that depend on the same "Budget" worksheet.

The 2 worksheets, "yyyy" and "yyyy_2", were supposed to have an equivalent set of conditional formats. Since the 2 worksheets react differently to the same action, we could conclude that they were/are not really equivalent.

Now it is up to the devs to evaluate whether there is really an unexpected behavior / bug.

Repro in:
Version: 7.4.5.1 (x64) / LibreOffice Community
Build ID: 9c0871452b3918c1019dde9bfac75448afc4b57f
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: en-US (es_AR); UI: en-US
Calc: CL

Repro also in:
Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 9788a565b3241d1bd62394b9e29c322361d05f80
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (es_AR); UI: en-US
Calc: CL threaded

Also repro (as steps in comment 19) in:

Version: 7.0.0.3 (x86)
Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e

and in:

Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: c3bd52f81bf733a0b9b0560794a54b2ac1e0f444
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (es_AR); UI: en-US
Calc: CL threaded


whereas with:

_ LibreOffice 3.3.0 
_ Version: 5.4.7.2
_ Version: 6.0.0.3
Build ID: 64a0f66915f38c6217de274f0aa8e15618924765

I repeated the steps in comment 19 and worksheet yyyy_2 behaves the same as yyyy, thus it's a regression that started somewhere between version 6 and 6.1.5.2.


See also META bug 87351 bug 116221 bug 116222.

(In reply to ady from comment #19)
> Finally!
> 
> Steps to reproduce:
> 1. Open attachment 185308 [details]
> 2. On the _3rd_ worksheet, named "yyyy", cell C19, enter the number: 119
> [ENTER]
> 3. Duplicate Worksheet "yyyy".
> 4. Worksheet "Budget", select entire row 24, [CTRL]+[+]
> 5. Compare conditional format of $yyyy_2.C19 vs $yyyy.C19
> 
> Note that the CF for cell $yyyy.C19 refers to "$Budget" (as an _absolute_
> reference to the worksheet). Hence, both worksheets, the original "yyyy" and
> the copy "yyyy_2", contain CF that depend on the same "Budget" worksheet.
> 

bisected to  e8e8d5c930bd2f68a4d874d01d4734b9a0beaa8b is the first bad commit
commit e8e8d5c930bd2f68a4d874d01d4734b9a0beaa8b
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Thu Dec 14 01:50:12 2017 -0800

    source 3f614f431475e1bf3bb3bbeac59b0681309628b7

https://gerrit.libreoffice.org/c/core/+/45656

Adding CC to Eike Rathke

Sorry, it's Mike Kaganski's patch.

To comment 14, comment 19:
I disagree that this is a bug. Yes, this is a behavior change. However, both behaviors have their benefits and logic, and merging the multiple CFs was done to avoid much worse problems.

OTOH, if there is a way to only merge ranges on copy of cells, not on sheet copy, that would be a possible improvement.

(In reply to Mike Kaganski from comment #23)

> I disagree that this is a bug. Yes, this is a behavior change. However, both
> behaviors have their benefits and logic, and merging the multiple CFs was
> done to avoid much worse problems.
> 
> OTOH, if there is a way to only merge ranges on copy of cells, not on sheet
> copy, that would be a possible improvement.

IDK about pros and cons, because I have no idea which issues prompted such behavior's change. BTW and FWIW, I don't recall seeing anything about such thing in any Release Notes – maybe I just missed it. Any links to any kind of info about this, anywhere?

What I do know is that, as a simple common user, I would expect that a copy of a worksheet would behave the "same" as the original. If a formula is using an absolute reference for a worksheet, I would expect to be kept in the newly-pasted copy with the same absolute reference. The same goes for a CF that depends on an absolute reference.

I have to admit that, even if I would had known about such behavior's change, I'm not sure I would had realized I was about to make a mistake in some common "duplicate sheet" action. Of course this is speculation ATM. IOW, I could understand the frustration of users finding out that what was supposed to be a copy, it is not really an exact copy. That is, if they happen to find out about it.

(In reply to ady from comment #24)
> I don't recall seeing anything about such thing in any Release Notes – maybe I
> just missed it. Any links to any kind of info about this, anywhere?

Indeed, there is the info - right following the commit link, there is a reference to tdf#95295. And since that was a bug fix, just one of hundreds bugfixes, it's not something that goes to the release notes. (And I didn't think about this behavior change back then; it wasn't intentional; if I did, it wouldn't change things, I'd only mention that in the commit message and in the bug.)

Again: if someone finds a way to improve the logic of that fix, that would be great.

(Meta bugs should be in Blocks rather than See Also.)

I observed the same issue in a slightly different way:

In my company we use a large table where you enter your finished work. Because the way the file is saved is not very meant for the way we do it, the progress within the file is often not saved.
The result is that some workers saved the file to their own PC, edit it over there and just copy the effected lines. This messes with the range/area of the conditional formatting in the original file.

The original range is this:

=$C$3:$D$1503


The current range of this year (2023-02) is this:

=$C$3:$D$55;$C$58:$D$94;$C$96:$D$1503


The end result of last year was this:

=$S$3:$T$23;$S$25:$T$109;$S$111:$T$127;$S$129:$T$140;$S$142:$T$215;$S$218:$T$230;$S$232:$T$247;$S$249:$T$254;$S$256:$T$291;$S$293:$T$316;$S$349:$T$356;$S$358:$T$372;$S$374:$T$398;$S$479:$T$598;$S$400:$T$477;$S$318:$T$347;$S$600:$T$606;$S$608:$T$616;$S$618:$T$677;$S$681:$T$696;$S$699:$T$700;$S$721:$T$725;$S$702:$T$719;$S$727:$T$816;$S$818:$T$873;$S$878:$T$934;$S$938:$T$964;$S$966:$T$974;$S$976:$T$978;$S$980:$T$1503


The end goal would be that you could somehow protect the conditional formatting against unwanted changes. I'm not sure if this bug report/feature request has the same core issue, but it sounds familiar enough to me.

(In reply to BDF from comment #27)
> I observed the same issue in a slightly different way:
> ............
> =$S$3:$T$23;$S$25:$T$109;$S$111:$T$127;$S$129:$T$140;$S$142:$T$215;$S$218:
> $T$230;$S$232:$T$247;$S$249:$T$254;$S$256:$T$291;$S$293:$T$316;$S$349:$T$356;
> $S$358:$T$372;$S$374:$T$398;$S$479:$T$598;$S$400:$T$477;$S$318:$T$347;$S$600:
> $T$606;$S$608:$T$616;$S$618:$T$677;$S$681:$T$696;$S$699:$T$700;$S$721:$T$725;
> $S$702:$T$719;$S$727:$T$816;$S$818:$T$873;$S$878:$T$934;$S$938:$T$964;$S$966:
> $T$974;$S$976:$T$978;$S$980:$T$1503
> 
> ...

It is not the same, what you have is for the same conditions a lot of different ranges. You know your sheet, but maybe with S3:T1503 does the same. 

I have just tested, adding at data with the CF at the end of the range, expand that range, doesn't add a new one.

Maybe https://bugs.documentfoundation.org/show_bug.cgi?id=133027