We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to do this special lookup in external file? [closed]

asked 2018-07-05 00:10:59 +0200

marcusman gravatar image

updated 2018-07-05 00:17:08 +0200


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

image description

image description

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-24 13:10:00.345659

4 Answers

Sort by » oldest newest most voted

answered 2018-07-07 08:39:12 +0200

pierre-yves samyn gravatar image


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.


edit flag offensive delete link more


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

erAck gravatar imageerAck ( 2018-07-07 23:12:58 +0200 )edit

answered 2018-07-05 00:49:26 +0200

erAck gravatar image

updated 2018-07-06 23:22:58 +0200

Seems to be a bug, please report at http://bugs.documentfoundation.org/ 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 & "")

edit flag offensive delete link more


Please link the bug report to this thread then.

Lupp gravatar imageLupp ( 2018-07-05 10:32:32 +0200 )edit
erAck gravatar imageerAck ( 2018-07-06 20:45:51 +0200 )edit

answered 2018-07-07 14:09:41 +0200

marcusman gravatar image

In the meantime the bug was filed, see here: https://bugs.documentfoundation.org/s...

They suggested a workaround that looks like this:


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

edit flag offensive delete link more


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. :-)

Mike Kaganski gravatar imageMike Kaganski ( 2018-07-09 07:50:18 +0200 )edit

answered 2018-07-09 07:42:32 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-07-05 00:10:59 +0200

Seen: 735 times

Last updated: Jul 09 '18