Issue 103331 - OFFSET returns Err:504 on reference to another calc file
Summary: OFFSET returns Err:504 on reference to another calc file
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 3.1
Hardware: All All
: P4 Trivial with 2 votes (vote)
Target Milestone: 3.4.0
Assignee: kla
QA Contact: issues@sc
Keywords: regression
: 108198 (view as issue list)
Depends on:
Blocks: 117389
  Show dependency tree
Reported: 2009-07-05 09:13 UTC by paulwolstenholme
Modified: 2017-05-20 10:30 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

testcase (8.30 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-03-15 14:13 UTC, ooo
no flags Details
referred file (7.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-03-15 14:13 UTC, ooo
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description paulwolstenholme 2009-07-05 09:13:25 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
The formula will be similar to:

Note that the formula result is Err:504 rather than the value of the cell in the
first calc document.

Same result on WinXP.
Comment 1 ooo 2009-07-06 14:06:27 UTC
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.
Comment 2 elysch 2009-07-29 15:35:17 UTC
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 

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 
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:

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:

I hope I made myself clear.

Comment 3 elysch 2009-07-29 15:36:08 UTC
Added me as cc
Comment 4 ooo 2009-07-29 20:02:28 UTC
For D*() functions there is a separate issue 102750
Comment 5 elysch 2009-07-30 04:32:59 UTC
Thank you "er"

I'll copy my post there.
Comment 6 ooo 2009-09-22 15:50:42 UTC
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
Comment 7 Regina Henschel 2010-01-10 12:13:08 UTC
*** Issue 108198 has been marked as a duplicate of this issue. ***
Comment 8 vitriol 2010-02-19 09:46:30 UTC
Add me to CC
Comment 9 ooo 2010-08-26 14:06:19 UTC
Won't have time to work on this for OOo3.3, re-targeting to OOo3.4
Comment 10 niklas.nebel 2011-03-04 13:09:40 UTC
Code change reviewed.
Comment 11 ooo 2011-03-04 13:47:19 UTC
In 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
Comment 12 ooo 2011-03-15 14:13:13 UTC
Created attachment 76106 [details]
Comment 13 ooo 2011-03-15 14:13:39 UTC
Created attachment 76107 [details]
referred file
Comment 14 ooo 2011-03-15 14:42:09 UTC
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.
Comment 15 kla 2011-03-21 12:44:18 UTC
Seen ok in cws calc66 -> verified