Bug 72440 - Function Wizard Range Selection Resulted in #REF Error
Summary: Function Wizard Range Selection Resulted in #REF Error
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta2
Hardware: All All
: high major
Assignee: Laurent BP
URL:
Whiteboard: target:6.1.0 target:6.0.2 target:5.4.6
Keywords: bibisected, needUITest, regression
: 113086 115721 116691 116922 (view as bug list)
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
 
Reported: 2013-12-07 16:15 UTC by p_kongstad
Modified: 2018-04-10 14:23 UTC (History)
16 users (show)

See Also:
Crash report or crash signature:


Attachments
Shows the vlookup wizard. (67.16 KB, image/png)
2013-12-07 16:15 UTC, p_kongstad
Details
VLookup (14.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-10-14 12:53 UTC, Jacques Guilleron
Details
Screenshot wizard (87.13 KB, image/png)
2017-10-14 18:13 UTC, m.a.riosv
Details
screenshotwizard (112.91 KB, image/png)
2017-10-15 10:31 UTC, Xavier Van Wijmeersch
Details
tail of terminal output from bibisect (2.32 KB, text/plain)
2017-12-27 19:27 UTC, Terrence Enger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description p_kongstad 2013-12-07 16:15:32 UTC
Created attachment 90416 [details]
Shows the vlookup wizard.

Hi,

When using the wizard to fill the parameters in the vlookup function is shows in '#REF!.A3:A3' in the search criterion.

In the past it would only show A3 as an example.

This is how it works in 4.1.4.1.
Comment 1 m.a.riosv 2013-12-07 23:03:58 UTC
Hi p_kongstad, thanks for reporting.

Please what is your detailed LibreOffice version. It is in Menu/Help/AboutLibreOffice, and it is possible to copy for paste here.

Can you detail the steps to reproduce the bug.

Recently has been fixed some issues about wizard.
https://bugs.freedesktop.org/show_bug.cgi?id=71667
Comment 2 p_kongstad 2013-12-12 11:14:20 UTC
Hi, I can no longer reproduce this error.
Comment 3 m.a.riosv 2013-12-12 18:49:22 UTC
Hi, thanks for the information, then we can change the status.

Please, if you see the bug again, reopen it.
Comment 4 Jean-Baptiste Faure 2017-10-11 14:01:18 UTC
(In reply to m.a.riosv from comment #3)
> Hi, thanks for the information, then we can change the status.
> 
> Please, if you see the bug again, reopen it.

The bug is back in versions 5.4.2 and master. Works in LO 5.3.4.
The wizard does not find the sheet name if criterion or matrix is on another sheet.

So reopen.

Best regards. JBF
Comment 5 Jacques Guilleron 2017-10-14 12:53:48 UTC
Created attachment 136971 [details]
VLookup
Comment 6 Jacques Guilleron 2017-10-14 13:46:39 UTC
Hi all,

I reproduce with
LO 5.4.0.0.alpha1 Build ID: 0b9f9bef65bb21ebb6a64aafad448f7f62dc824a
Threads CPU : 2; OS : Windows 6.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: CL
and upper versions
but not with
LO  5.3.6.1 Build ID: 686f202eff87ef707079aeb7f485847613344eb7
Threads CPU : 2; Version de l'OS :Windows 6.1; UI Render : par défaut; Moteur de mise en page : nouveau; Locale : fr-FR (fr_FR); Calc: CL
and later versions

To make appear the bug, the formula has to be located in a sheet after the sheet where you search, and the wizard has to be used.
Other functions, as the Sum function, produce the same result with the same conditions.
Comment 7 Jacques Guilleron 2017-10-14 14:01:31 UTC
*** Bug 113086 has been marked as a duplicate of this bug. ***
Comment 8 m.a.riosv 2017-10-14 18:13:50 UTC
Created attachment 136973 [details]
Screenshot wizard

I can't reproduce
Version: 5.4.2.2 (x64)
Build ID: 22b09f6418e8c2d508a9eaf86b2399209b0990f4
CPU threads: 4; OS: Windows 6.19; UI render: default; 
Locale: es-ES (es_ES); Calc: group
Comment 9 Xavier Van Wijmeersch 2017-10-15 10:31:56 UTC
Created attachment 136981 [details]
screenshotwizard

Problem still there

Version: 5.4.2.2.0+
Build ID: SlackBuild for 5.4.2 by Eric Hameleers
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group

Version: 6.0.0.0.alpha0+
Build ID: 8eacd3be08bf6e1a97900624611822de9b00a379
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 10 Gert 2017-11-19 10:33:30 UTC
LO 5.4.3.2

In reply to Jacques Guilleron comment 6

Quote:
"To make appear the bug, the formula has to be located in a sheet after the sheet where you search, and the wizard has to be used.
Other functions, as the Sum function, produce the same result with the same conditions."

The wizard works well when the formula is located in the sheet and the information looked for is in an after sheet.

F.i. Sheet1 - sheet2 - sheet3 - sheet 4 - sheet 5.
The wizard works well if the f0rmula is place in sheet1 and the search is in sheet2, sheet3, sheet4 or sheet5.
However when the formula is placed in sheet3 and the search is in sheet4 of sheet 5, the wizard fails.
Comment 11 Kevin Suo 2017-11-22 10:53:15 UTC
Edited Summary to reflect the issue.
Comment 12 Kevin Suo 2017-11-22 10:59:30 UTC
Just to clarify, this happens to all functions which allow range parameter. Copying the steps to reproduce explained in bug 113086.

Steps to Reproduce: 
1.Create a new Spreadsheet 
2.Create a new sheet named "Sheet2" 
3.Switch to sheet "Sheet2" 
4.Select any Cell in Sheet, then click function wizard 
5.Choose any function, for example ABS, then click "Next >>" button. 
6.Click "Select" button right at Number textbox 
7.Choose a range in Sheet1 

Actual Results: 
In select Dialog, show "$Sheet1.A1:A19" 

Expected Results: 
In select Dialog, show "$#REF!.A1:Sheet1.A19"
Comment 13 Cor Nouws 2017-12-18 10:43:23 UTC
(In reply to Kevin Suo from comment #12)

> Actual Results: 
> In select Dialog, show "$Sheet1.A1:A19" 
> 
> Expected Results: 
> In select Dialog, show "$#REF!.A1:Sheet1.A19"

The other way round ;)
Comment 14 Cor Nouws 2017-12-18 10:45:11 UTC
works OK indeed in 4.0.6 > regression
Comment 15 Terrence Enger 2017-12-27 19:27:02 UTC
Created attachment 138696 [details]
tail of terminal output from bibisect

Working on debian-buster in the till54 bibisect repository, I see that
the the bug came into LO somewhere in the 56 or so commits:

          commit    date        s-h
          --------  ----------  --------
    good  2bb2885d  2017-04-19  e175f9f4
    bad   e3c4358c  2017-04-20  d11746a8

In that range I notice commit ca8f4a66 Resolves: tdf#90799 generate
absolute sheet for pointer-selected references".

I am removing keyword bibisectRequest and adding bibisected.
Comment 16 Cor Nouws 2017-12-27 21:02:32 UTC
@erack: 

(In reply to Terrence Enger from comment #15)
> In that range I notice commit ca8f4a66 Resolves: tdf#90799 generate
> absolute sheet for pointer-selected references".

can you please have a look?
Comment 17 Cor Nouws 2017-12-27 21:03:55 UTC
/me noting that the patch is from 2017-04-19, I don't expect it influences 4.2.0, but ..;)
Comment 18 Jacques Guilleron 2018-02-04 15:43:32 UTC
Hi all,

issue still present in
LO 6.1.0.0.alpha0+ Build ID: 6fc9d4a482ab50a1bf8fefb1dae2a6ded3c7e3dd
CPU threads: 2; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-01-24_04:37:19
Locale: fr-FR (fr_FR); Calc: CL
when the VLOOKUP function is located in a sheet located after the lists where to search.
Comment 19 Commit Notification 2018-02-12 16:15:24 UTC
Laurent BP committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d69017c8a17be21657ea7ab9d37023ee59116799

tdf#72440 Abs sheet ref must be given

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 20 Commit Notification 2018-02-12 20:07:51 UTC
Laurent BP committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c5647bd503b1d72229492b55257b0e3eb8f42464&h=libreoffice-6-0

tdf#72440 Abs sheet ref must be given

It will be available in 6.0.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 21 Commit Notification 2018-02-12 20:08:02 UTC
Laurent BP committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=505832b92e0e2a395ef22424de33168a76bcc2c3&h=libreoffice-5-4

tdf#72440 Abs sheet ref must be given

It will be available in 5.4.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 22 Jean-Baptiste Faure 2018-02-12 21:02:18 UTC
Verified fixed on LibreOffice 6.0.2.0+ and master, both built at home under Ubuntu 16.04 x86-64.

Thank you very much for this patch.

Best regards. JBF
Comment 23 Jacques Guilleron 2018-02-14 09:36:38 UTC
Work as aspected with
LO 6.1.0.0.alpha0+ Build ID: 3c913c3844acae8ee0d80ab174133bdc7677efea
CPU threads: 2; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-02-14_00:19:27
Locale: fr-FR (fr_FR); Calc: CL
Thank you Laurent.
Comment 24 Eike Rathke 2018-02-14 15:36:26 UTC
*** Bug 115721 has been marked as a duplicate of this bug. ***
Comment 25 raal 2018-03-29 14:56:30 UTC
*** Bug 116691 has been marked as a duplicate of this bug. ***
Comment 26 Eike Rathke 2018-04-10 14:23:15 UTC
*** Bug 116922 has been marked as a duplicate of this bug. ***