Created attachment 198783 [details] Example file from Calc This is a followup to bug 164843 When the attached example file is opened in Excel then the calculation with the named range with relative reference seems to be correct - but in Calc, the result is 0 even after fixing the current #REF! error in the Manage Names dialog. 1. Open attached document in Excel and Calc -> Observe in Excel that the "testrange" named range is handled correctly in the Name Manager dialog, and calculation in the AD column is correct 2. In Calc, there is currently a #REF! error visible in the Manage Names dialog. Fix that manually for the "testrange" named range -> After a bit of wait, the results in the AD column are all 0. For comparison the AE column contains the same formula but with an absolute range, which always gives 2000 as result in both Calc and Excel - so the only difference is in the handling of the "testrange" named range. Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 43fc662520e5488cbeadd6eb60a24374a837dca4 CPU threads: 14; OS: Windows 10 X86_64 (build 19045); UI render: Skia/Raster; VCL: win Locale: hu-HU (hu_HU); UI: en-US Calc: threaded
Created attachment 198784 [details] Result of the formula in Excel 2016 and in Calc master after fixing the ref error
The same issue opening with 24.8, but correcting the named range with 1 instead #ref! while in a cell of row 1. Seems to work and saving it then opens fine with 25.8 Excel opens it with 1 as row, but with @ at the beginning of the formula, to make it a non array formula.
This is not a real bug, but the MS-Office importing wrongly the *.ods files. It is possible to create the same results with the same name range and function in MS-Office too, just need to create the namedrange with a relative position AC2000. For that you need to select the AC2000 cell before creating the namedrange.