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.



Usually I use “Data”->“Validity…” and select “Cell range” and use a formula like the following:


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:


The following screenshot images illustrate the problem:

image description

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.

Seems to be a bug, please report at and attach the sample document there. Thanks.

So, the finding is: an external reference is not supported as a final result in the THEN or ELSE paths of an IF jump if in array/matrix mode.

A workaround is to not have the external reference be the final IF result, introducing an operation such as concatenating with an empty string (…&"") in this case of the Validity expression helps:

IF('problem_2_external_file.ods'#$external_data.B$1:B$9999=B14,'problem_2_external_file.ods'#$external_data.C$1:C$9999 & "")

Please link the bug report to this thread then.



For information, another workaround would be to use the formula OFFSET as in F18 (yellow list).


MATCH and COUNTIF are used to determine the offset.

By the way, since I’m not sure I understood your remark correctly:

My next workaround would be to add the relevant data to the main file

you would have no problem (F14 green list) if you link (SheetInsert Sheet from filelink) the external sheet. But maybe that’s what you’re talking about.


Note that using OFFSET() adds more overhead to the calculation than the below suggested concatenation.

In the meantime the bug was filed, see here:

They suggested a workaround that looks like this:


So, you simply add a concatenation with the empty string at the end: &""

Heh, interesting how you didn’t look at the answer by @erAck here, who also answered there on the bug. And who have fixed this for 6.0.6. :slight_smile:

Best is to create named ranges. Gather category items in columns and make “enough” column selection (so that you can add things later) for each category as a named range.