Data Range not updating after adding new data

[bstabens]

I have a file with two sheets.
So the cells X and Y are on SheetA, the data range to select from is on SheetB.
SheetA has an elaborate setup of sumifs, all in the form of
“addup all values from the data range on SheetB in column 6 if in col1 there is the same value as in SheetA’s cellX and in col2 there is the same value as in SheetA’s cellY”

Some of the values referred will only exist later in time, as this is essentially a monthly report and I have already prepared all months of the year even if these are not present at the moment.

Now every few months I insert rows I have copied from another (excel) sheet into my data range on SheetB. I modify my data range to include the new rows, I refresh it - but it seems all connection to SheetA is broken now. The cells who are supposed to work for “this month” won’t add up the values from the newly inserted lines of data.

I have, last time I encountered this, expanded my range by some lines. When I now put some made-up data into these already included, but previously empty lines, my old sums won’t update either. But changing already existing values will change the corresponding sumifs-sum on SheetA.

I have also tried to work around this bug by not referencing the data range by name, but by row numbers - tough luck, doesn’t work either.

In fact, even if I download the corresponding example from the official help (function_ifs.ods) and change some of the values there, the sumifs of that example won’t update either, which makes me think it’s something in the preferences that “loses” this functionality after each save, or better each reopening.

Thanks for helping.

[Zizi64]

Please upload an ODF type sample file here.

[bstabens]

This is personal data, so I won’t give you the original file, sorry. But as I said, the same bug happens with the official function_ifs.ods, so it’s certainly not file-related. And if it is something to do with my local preferences, it wouldn’t show up for you anyway.

[bstabens]

Try the following: Download function_ifs.ods
Edit:
Oh, sorry, you’re right - official example file from libreoffice.org for download here:

/Edit

Look at the data ranges (Data - Select Range). In my case, I have no predefined ranges in this file.
Now create a data range from A2 to E40, leaving 10 “empty” lines in the new data range.
Copy a line about “Model_01” into the first empty line and change the “Show Room” value to 10 000.
The already existing cells with formula will not update.
Change the first “Showroom” value of “Model_01” to 10.000 and it will change the numbers.
That’s what happening in my file. New lines will not be taken into account.

[karolus]

@bstabens

Try the following: Download function_ifs.ods

give us a chance to try … upload it!

[PKG]

https://wiki.documentfoundation.org/File:Calc_Functions_ifs.ods

[Villeroy]

menu:File>SaveAs… save under some other name.
Select all columns with text.
menu:Edit>Find/replace…
[X] Current Selection
[X] Regular expression
Find: [:alpha:]
Replace: X
[Replace All]

Now all the personal info is Xed out leaving dates and numbers with no context.
Upload that file.
Nobody here is interested in content. What actually counts is the structure.

@bstabens

elaborate setup of sumifs

sounds bad. Are you aware of pivot tables?

[bstabens]

If all that counts is the structure, please note this comment Data Range not updating after adding new data - #4 by bstabens

My elaborate setup of sumifs is dependent of two criteria that have to be true for the value to be summed up. If A like cellA, and B like cellB, add value C to sum. I’ve looked into pivot tables, but they seemed to be a bit overpowered for that task.

[Villeroy]

pivot_106805.ods (32.1 KB)
Simple pivot tables on 2 sheets. On the second sheet, I normalized the source data, so the pivot table needs only one field per function.

[Villeroy]

Continued:
The original table in the sample file is a cross table (aka contingency table) where you need to enter the right value below the right column label. Values are categorized by their columns. Any single value of a new category would require a new column.
The table on the second sheet is a normalized table where all values are in the same column and the location names are attributes of a separate column rather than column labels.
The information of both tables is exactly the same.
I can enter data into newly inserted cells and the SUMIF formulas update. Pivot tables don’t update fully automatically because the whole table is rebuilt. Right-click>Refresh does that.

[bstabens]

So, the weirdness began…
I exported the data into a CSV file and regexp’d the data into something boring.
I then reopened in Calc and tried to convert the data into a pivot table, and it showed all column names quite nicely, but not a single sum!
The currency values didn’t get recognized as “numbers” nor “strings”. I know because the math formula ABS threw an error, but the text formula numbervalue did too! So obviously the ciphers in my cells were neither?

I went back to the CSV and changed the decimal separator from . to , in the csv. And, suddenly, I got all the sums in my pivot table.

Thinking this could be the answer, I went back to my original file (the one not converted and reconverted, with the sumifs and not redacted) and changed the decimal separator here, too, via a regex formula directly in the cell.

No sums in the pivot table.
Already a bit more experienced, I tried to convert whatever was in that cell now to an ABS or a NUMBERVALUE. Both times I got a “Value” error.

I attached the files as ODS, test_pivot having a dot as decimal separator, and test_pivot2 with the comma as decimal separator. Since I have a german layout, but use libreoffice in english, it might have to do with that.
test_pivot.ods (84.3 KB)
test_pivot2.ods (85.9 KB)

I strongly feel that I don’t like the idea of exporting data into csv and correcting all decimal separators to get this running.

[Villeroy]

Why csv? Csv is a database exchange format, a mere converntion rather than file format. Csv stores plain text. Csv makes everything more complicated. Depending on the export and import options of the respective application, numeric strings, dates, times etc. may be interpreted correctly or not. When importing csv, LibreOffice shows an import dialog which helps to import thousands of different flavors of csv.

[bstabens]

CSV stores plain text, correct, so no chance of some hidden directives. It’s in the name - comma separated values. It’s easy to edit and easy to convert into a calc sheet again, be it for libreoffice or any other office app.
Honestly, it feels a bit weird to talk about “importing different flavors of csv” when all there is to it is telling libreoffice which designator stands for “next column”. You might even get fancy and already tell the app which kind of data class to assign.

[erAck]

@bstabens

I tried to convert whatever was in that cell now to an ABS or a NUMBERVALUE. Both times I got a “Value” error.

Of course. In test_pivot.ods the content of cell E2 is of type Text, having it forced to be right aligned and applied a currency display format does not change anything about that. ABS() of text returns #VALUE!, at least if the localized value text does not match the current locale (your’s is German that uses , comma decimal separator instead) or the detailed conversion options are set strict (Tools → Options → Calc → Formula, Details; best setting is Generate #VALUE! error to catch errors early, worst setting is the Convert also locale dependent default because Excel does that nonsense. For NUMBERVALUE() specify the second parameter, decimal separator, otherwise if the text does not represent an integer value the result is #VALUE!. So =NUMBERVALUE(E2;".")
See NUMBERVALUE online help.

=MONTH(F2) and =YEAR(F2) are #VALUE! because F2 has a text result of REGEX(), both functions expect numeric date values instead.

If you imported CSV you missed to set the correct import options. An import locale that matches the data’s decimal separator and best also date format, e.g. English-UK and activate Detect special numbers, or specify column types individually, i.e. date D/M/Y for the first column and English-US for the currency column if the separator used is . dot. See Text Import online help.

To repair already imported data see this FAQ.