Description: After upgrading from LO7.3.6 to LO7.4.1 two spreadsheets I use on a regular basis failed to calculate correctly. The files consist of several sheet and an overview page that uses formulas with INDIRECT() in combination with several named ranges. Several hours of debugging later, it boils down to named ranges having a minimum name length of 8 characters now. So named ranges already defined, using a shorter name, are failing more or less silently. The formula just results in "#VALUE!". Renaming the ranges and using longer names resolved the issue. Steps to Reproduce: 1. Create a calc file with LO7.3 (or earlier). 2. Define a named range (e.g. "MINI"). 3. Use the named range for some calculations and confirm it works. 4. Open the file with LO7.4. Results break. Actual Results: The formulas failed and faulty results where calculated. Expected Results: If a minimum name length for named ranges is required, Calc should warn users when opening files using named ranges with shorter names. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.4.1.2 / LibreOffice Community Build ID: 40(Build:2) CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: de-AT (de_AT.UTF-8); UI: en-US Ubuntu package version: 1:7.4.1~rc2-0ubuntu0.20.04.1~lo1 Calc: threaded
Can't reproduce. Naming a cell MINI and using =INDIRECT("MINI") works perfectly fine, also when created in LO 7.3.6 and loaded in 7.4.1 Please attach a small sample file that exposes the error.
(In reply to Sascha Nemecek from comment #0) > 2. Define a named range (e.g. "MINI"). Could that be actually e.g. "MIN1" (i.e., the last character being not capital "i", but digit "one"?) Because in 7.4, with column count increased to 16K, MIN is now a legitimate *column name*, with the last column having name "XFD", while previously it was "AMJ" (7.3 allowed to enable "very large sheets" in Calc's "Defaults" options).
(In reply to Mike Kaganski from comment #2) But even then, it works OK with Version: 7.4.1.2 (x64) / LibreOffice Community Build ID: 3c58a8f3a960df8bc8fd77b461821e42c061c5f0 CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: ru-RU (ru_RU); UI: en-US Calc: CL where the now-invalid "MIN1" range name imports OK from existing documents, and takes precedence over the cell name MIN1 in formulas. (IMO, an infobar with conflicts would be useful in this case - but that is a separate issue.)
@Mike Kaganski: This is good thinking and could be the correct lead. My files used "UST2022", "UST2021" and so on as defined name ranges (JFTR: UST == VAT, value added tax). The formulas we use are build as follows: ``` =SUMPRODUCT( INDIRECT("Betrag"&AD$3-0&"Brutto"); INDIRECT("UST"&AD$3-0)=$C$97 ) ``` Explanatory information: * This will sum up all values for the given tax rate (e.g. 20%). * `AD$3` defines the year in question (e.g. 2022). * `$C$97` defines the tax rate we are looking for (e.g. 20%). * So `"Betrag"&AD$3-0&"Brutto"` resolves to `Betrag2022Brutto`, which is a named range referencing column cells in another sheet. The scope of the named range is set to the overview sheet. * `"UST"&AD$3-0` resolves to `UST2022`, which is a named range referencing column cells in the same sheet as `Betrag2022`. The scope of the named range is also set to the overview sheet. * JFTR: For better a understanding, I reduced the complexity of the formula. Additional information: When editing the names ranges ("Manage Names", <Strg>+<F3>), the dialog shows the following notice, when editing the "short" names, e.g. "UST2022": "Invalid name. Start with a letter, use only letters, numbers and underscore". As soon as I change the name to "UST_2022", the notice disappears and the formula is working again. That's why I assumed it's a minimum name length requirement.
Created attachment 182540 [details] A minimal reproducer Repro.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/4cb347ae1c62f9c8a53f17c3b7957ab68aade95e Resolves: tdf#151046 INDIRECT() try named expressions and DB range names first It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/140154 for 7-4
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/4f66fada43cf50767066c756def7eb39654e376b Resolves: tdf#151046 INDIRECT() try named expressions and DB range names first It will be available in 7.4.2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/dfa01bb05cc82811cdc306285a31c51816f81c6a tdf#151046: sc_subsequent_filters: Add unittest It will be available in 7.5.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.