Description: Opening xlsx file with =INDIRECT() formula shows #REF! error instead of actual value referred. Steps to Reproduce: 1. Open the attached xlsx file in Calc. 2. Observe the values in F2:F7 in Sheet1. Actual Results: The cells in the range F2:F7 shows #REF! error. Expected Results: The cells in F2:F7 should be the corresponding values in A2:A7. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.3.7.2 / LibreOffice Community Build ID: 30(Build:2) CPU threads: 8; OS: Linux 5.19; UI render: default; VCL: gtk3 Locale: en-US (en_IN); UI: en-US Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.2 Calc: threaded This is also present in the current master build in Linux 64 bit.
Created attachment 187351 [details] Bug repro document
Created attachment 187352 [details] screenshot in LO
Created attachment 187353 [details] screenshot of MSO
I get Err:502 ("Invalid argument") in OOo 3.3, but I think indirect() wasn't supported back then? I get #REF! ("Not a valid reference") in 6.0.0.3 and in a recent master build: Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: 1349f140fcc49e5da78482ca3db09663ccdae0a9 CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded No effect from changing Tools > Options > Calc > Formula > Detailed Calculation Settings. Dennis, when fixed, would the wiki page need updating? https://wiki.documentfoundation.org/Documentation/Calc_Functions/INDIRECT
I think the problem is that "ScoreNames" is not correctly identified. For example, it is listed in Manage Names, but not in the Names box.
The problem is INDIRECT currently does not handle a name that resolves to table structured reference. This will be fixed by a master port of https://gerrit.libreoffice.org/c/core/+/151720. This is being reviewed.
Dennis Francis committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/4536694edb7e564ea1f4ae871e9107f52ed0972f tdf#155369 ScIndirect: handle names that resolve to... It will be available in 7.6.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.
Fixed in master branch.
(In reply to Stéphane Guillou (stragu) from comment #4) > I get Err:502 ("Invalid argument") in OOo 3.3, but I think indirect() wasn't > supported back then? INDIRECT() was, but table structured references aren't supported at all in OOo.
Dennis Francis committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/52e7edf4a5980b4451551a33a5f4e5432d27a9e0 tdf#155369 ScIndirect: handle names that resolve to... It will be available in 7.5.5. 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.
Fix verified in: Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community Build ID: f3aab159f1c1e00c25e6b4ca1e50813bc343f4f3 CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Thank you Dennis et al.!