[Libreoffice-bugs] [Bug 118561] New: Calc: How to do this special lookup in external file?

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118561

            Bug ID: 118561
           Summary: Calc: How to do this special lookup in external file?
           Product: LibreOffice
           Version: 5.3.7.2 release
          Hardware: All
                OS: Windows (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: marcus at sternenschwarm.de

Description:
It's about doing a lookup in an external .ods file from a main .ods file.
A testcase with 2 simple files is included that demonstrates the problem.
A series of 4 screenshot images gives a quick overview of the problem.

Posted it in other forum first. Person there says it might be a bug. See:
https://ask.libreoffice.org/en/question/159779/how-to-do-this-special-lookup-in-external-file/

Not sure if this is a bug 
-or-
The formula is just wrong
-or-
This mechanism is not possible with LibreOffice

Steps to Reproduce:
1. Store the 2 .ods files in the same directory.
2. Open the main .ods file.
3. Do the things that you see in the screenshot images.
4. (The cell where the problem occurs is marked yellow/orange)

Actual Results:
Lookup in the same file works without problems.
Lookup in the external .ods file doesn't work (this way).

Expected Results:
Lookup in the external file should be possible in a similar fashion.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Not sure if possible with a slightly different formula.
People in the ask.libreoffice.org say it may be a bug.
See:
https://ask.libreoffice.org/en/question/159779/how-to-do-this-special-lookup-in-external-file/

I have a tandem of 2 .ods files - one that holds a lot of data and one that is
the interface.
C:\fakepath\problem_2_main_file.ods
C:\fakepath\problem_2_external_file.ods
Usually I use "Data"->"Validity..." and select "Cell range" and use a formula
like the following:
IF($data.$B$1:$B$9999=B6,$data.$C$1:$C$9999)
This formula checks creates a list of things to select from (column "C" in
"data") by filtering only those where column "B" matches a certain category.
I try to use a similar formula when looking up in the external .ods file, but
this formula fails:

IF('file:///E:/Problem2/problem_2_external_file.ods'#$external_data.B$1:B$9999=B14,'file:///E:/Problem2/problem_2_external_file.ods'#$external_data.C$1:C$9999)

The following screenshot images illustrate the problem.

I will try to do this with Microsoft Office since I'm out of options now - if
it works there, then it should(!) work in LibreOffice too.

Maybe the syntax of the formula is wrong? Or perhaps I did not think of
something else? One alternative might be to use a real database and a C++ app
or something like that. But I want to use two Calc files instead - one that
holds the data and one that is the user interface.

My next workaround would be to add the relevant data to the main file - which
is a problem. You see, the idea was to separate the 2 files because the
database is a big file (10 MegaByte) whereas the other file that uses this data
is only 100 KiloByte in size and will be used like a 1000 times to represent a
1000 times the data in different combinations. Thus, I would have 1001 x 10 MB
versus 1000 MB + 1000 x 0,1 MB which means 1000 times more disk space used when
having the data all in one file.

Also ... I separated the two because if things change in the database then only
the database will need an update. The other 1000 files which reference the
other .ods file won't need an update. The data .ods is located in a folder
above the interface .ods file.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180705/8e993ab2/attachment-0001.html>