Hi,
On 2024/01/27 20:06:17 Damjan Jovanovic wrote:
> On Sat, Jan 27, 2024 at 5:02 PM Czesław Wolański <cwo...@apache.org> wrote:
> (...)
> > Please give an example of a non-functioning intersection.
> > AFAICT it works as per specification
> > (in short: return the value from the cell on the same row or column as the
> > formula;
> > otherwise, return an Error).
> >
> If you fill A1-A5 with:
> Letters
> a
> b
> c
> d
>
> then go to C3, and type =VLOOKUP('Letters', 'Letters', 1, 0)
> you'll get an error.
>
> If on another page or another spreadsheet you fill A1-A5 with:
> Numbers
> 15
> 16
> 17
> 18
>
> then go to C3 and type =VLOOKUP('Numbers', 'Numbers', 1, 0)
> you'll get 16.
>
> Why? Apparently because (at least in AOO) intersection only works correctly
> for numerical values, not strings.
> Even in C4 if you just do ='Numbers' you'll get 17, but if you do
> ='Letters' on the other page/spreadsheet's C4 you'll get a 0 instead of "c".
Seeing is believing, thank you!
LibreOffice is affected too.
For strings it seems to work correctly when using
- a named range (Insert ⟶ Names ⟶ Define)
or
- a database range (Data ⟶ Define Range).
>
> Is that a bug?
>
I do think so.
Calc Help topic "Recognizing Names as Addressing" states:
"This function is active by default. To turn this function off,
choose Tools - Options - OpenOffice Calc - Calculate and
clear the Automatically find column and row labels check box."
You will find a similar statement in LibreOffice 7.4 Help [1],
but in LibreOffice 7.5 Help one reads [2]:
"Automatically finding labels is a legacy feature and deactivated by default
as it can produce nondeterministic behavior depending on actual document
content.
To turn this function on, choose Tools - Options - LibreOffice Calc - Calculate
and
mark the Automatically find column and row labels check box.
Using defined labels instead is always possible and behaves similar but in a
defined way."
It looks like the right path for AOO as well.
The user should be warned of the risks involved and
viable alternative(s) should be pointed out.
Regards,
Czesław
---=== Ref. ===---
LibreOffice Help for Calc, topic "Recognizing Names as Addressing"
[1] version 7.4
https://help.libreoffice.org/7.4/en-US/text/scalc/guide/address_auto.html#par_id3155443
[2] version 7.5
https://help.libreoffice.org/7.5/en-US/text/scalc/guide/address_auto.html#par_id3155443
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@openoffice.apache.org
For additional commands, e-mail: dev-h...@openoffice.apache.org
