Apache OpenOffice (AOO) Bugzilla – Issue 103331
OFFSET returns Err:504 on reference to another calc file
Last modified: 2017-05-20 10:30:57 UTC
The offset function used to work up to 3.0.1 but in OOo 3.1.0 it returns Err:504 whenever the reference field refers to another calc document. To replicate: 1) Create a new calc document and save it as the default name (Untitled 1.ods) on the desktop. 2) Create a second calc document. (There is no need to save it.) 3) In the second document, select cell A1 of sheet1. Use the function wizard to enter function OFFSET. Set the 'rows' field to 0 and the 'columns' field to 0. With the cursor in the 'reference' field, click the first spreadsheet and a cell (e.g. A1) within that spreadsheet. Click OK on the function wizard to enter the formula. The formula will be similar to: =OFFSET( 'file:///C:/Documents%20and%20Settings/PaulW/Desktop/Untitled%201.ods'#$Sheet1.A1; 0; 0 ) Note that the formula result is Err:504 rather than the value of the cell in the first calc document. Same result on WinXP.
The external reference in this case must not to be resolved to a value before feeding it to OFFSET, but preserved instead to be able to shift offsets. This could mean to treat external references differently depending on where in a formula they occur, which would be extremely nasty. As OFFSET mostly is used to form dynamic ranges, a new resulting external reference should also not be resolved. This in fact may mean to not resolve external references at all until needed, which would imply a completely different handling of ocExternalRef.
I don't know if this is the same problem, but it sounds like (to me). If you thing I need to open a new issue, let me know. I also get Err:504 using DGET. But it was working just fine up until version 3.0.1 It can be reproduced (almost) the same way, as metioned before. 1) Create a new calc document 2) Fill in a simple table with "field names": Add col1, 1, 2, 3 to A1, A2, A3 and A4 respectively; col2, a, b, c to B1, B2, B3 and B4 respectively. 3) Save it with the default name (Untitled 1.ods) 4) Create a second calc document. (There is no need to save it.) 5) In the second document, enter col1 in A1 and 2 in A2 6) In the second document, select cell C1 of sheet1. Use the function wizard to enter function DGET. 6.a) Click on "Database" edit box, then go to the first document and select A1:B4 6.b) Click on "Database field" edit box, and enter "col2" (with double quotes) 6.c) Click on "Search criteria" edit box, and select A1:A2 in the same document. The formula should be similar to: =DGET('file:///C:/Documents%20and%20Settings/Ely/Escritorio/Untitled% 201.ods'#$Sheet1.A1:B4;"col2";A1:A2) When you click on the "ok" button, you get the ERR:504 error instead of the letter "b" If you put almost the same formula in the first document, you get the expected value. That is: Enter col1 in D1 and 2 in D2 of Untitled 1.ods in the first sheet. Then do the step number 6 I've just described, but all in the Untitled 1.ods file, and replacing A1:A2 in step 6.c for D1:D2... and you get the expected letter "b" at C1. The resulting formula looks like: =DGET(A1:B4;"col2";D1:D2) I hope I made myself clear. Ely
Added me as cc
@elysch: For D*() functions there is a separate issue 102750
Thank you "er" I'll copy my post there.
OFFSET() on an external sheet is a quite rare use case. As my tight schedule probably won't allow me to fix this and also the D*() functions' issue 102750 is targeted to OOo3.3, I'm targeting this to OOo3.3
*** Issue 108198 has been marked as a duplicate of this issue. ***
Add me to CC
Won't have time to work on this for OOo3.3, re-targeting to OOo3.4
Code change reviewed.
In cws calc66: changeset dd42e2373f79 http://hg.services.openoffice.org/cws/calc66/changeset/dd42e2373f79 M sc/formula/inc/formula/token.hxx M sc/formula/inc/formula/tokenarray.hxx M sc/formula/source/core/api/token.cxx M sc/inc/token.hxx M sc/source/core/tool/interpr1.cxx M sc/source/core/tool/interpr4.cxx M sc/source/core/tool/token.cxx You can observe the progress and possible integration date of CWS calc66 at http://tools.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fcalc66
Created attachment 76106 [details] testcase
Created attachment 76107 [details] referred file
Reassigning to QA for verification. Note that currently only positive values and cell references work as rows, columns, height and width arguments. Expressions can't be used, for this follow-up issue 117389 exists.
Seen ok in cws calc66 -> verified