Hello,
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:
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:
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.
