Download it now!
Bug 109254 - Allow toggling of absolute and relative references when working with Function Wizard
Summary: Allow toggling of absolute and relative references when working with Function...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.4.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Function-Wizard Function-Vlookup
  Show dependency treegraph
 
Reported: 2017-07-21 09:38 UTC by Jean-Sebastien Bevilacqua
Modified: 2017-08-14 20:13 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
usecase (9.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-07-21 15:47 UTC, Jacques Guilleron
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jean-Sebastien Bevilacqua 2017-07-21 09:38:38 UTC
Hello,

When you use the Function Wizard in Calc, and use the function VLOOKUP, the second parameter is a range of cell to search in.
The problem is that selected cells are without $ (absolute position).

So when you drag the cell (to extend the formula), the range is updated too (static range is better generally).

Example:

Currently: =VLOOKUP(A1;C1:D10;2;0)
After    : =VLOOKUP(A1;$C$1:$D$10;2;0)

Would it be possible to change this behavior in LibreOffice with a static range by default (with $) ?
What do you think about it ?

Sincerely,
Jean-Sébastien Bevilacqua
Comment 1 Xavier Van Wijmeersch 2017-07-21 13:30:48 UTC
Nice tough, but next to the function wizard there is a name box, where you can put a name for the range of cells you need in the vlookup. And so you can drag it down without changing the range array. And it is better way when the spreadsheet needs a data checkup, or upgrade.
Comment 2 Jean-Sebastien Bevilacqua 2017-07-21 13:46:47 UTC
Thanks Xavier for your answer, very interesting.
I tested it and it work great.

I would like more opinions about it to know if it's enough.
Comment 3 Jacques Guilleron 2017-07-21 15:47:44 UTC
Created attachment 134768 [details]
usecase

An example where this could be useful.
Comment 4 Jacques Guilleron 2017-07-21 17:45:20 UTC
Criterion can be used as name for E2:E120 and to replace the first argument.
Comment 5 m.a.riosv 2017-07-22 11:38:56 UTC
Maybe the feature should be 'F4 Toggle reference', working insidee the wizard when selecting a range.
Comment 6 Buovjaga 2017-08-14 14:45:09 UTC
(In reply to m.a.riosv from comment #5)
> Maybe the feature should be 'F4 Toggle reference', working insidee the
> wizard when selecting a range.

So this: https://help.libreoffice.org/Calc/Addresses_and_References,_Absolute_and_Relative#Absolute_Addressing

It seems we have consensus this is useful, so changing summary and setting to NEW.