CALC: Link to external data - two issues

[AlwynWellington]

Several years ago and using MS Excel 2007 I developed a system that has:

 a) text in four different sheets in the one .xlsx workbook
         - this book is passive, in effect a data base repository
         One sheet has about 40 columns for each index key with
         the "stuff" in each column being brief but some exceeds 255 char.
         Rows in the other three sheets consists of a index and one cell 
         containing text (often many thousands of characters) 
         In .xslx format total size is less than 2,000 kb.  
         This loads very quickly in LibreOffice 4.2

 b) a search engine in second workbook.  The Excel version has a 
         "name range" for each sheet in the "texts" workbook.
         vlookup function is used to search the "texts" book
         This "search" book has been ported to LibreOffice 4.2 without
         apparent difficulty.
         Except:
             It will retrieve data from the sheet with 40 columns
             It will NOT retrieve data from the other three sheets.

         I have tried porting the "texts" book into ODS format.  The
         port is successful, but data is not retrieved.

         Question: why will some otherwise successful vlookups work 
                        and not others?

I decided to start over.
I created a small “database” workbook with one sheet and 20 rows of two columns
In a second workbook I attempted to insert a “Link to External Data”.
The dialogue let me select a local file. I chose firstly my “texts” workbook. Then
the newly created (tiny) workbook.
In both cases the dialogue appeared to freeze as soon as the extrenal source was
selected.
In both cases I was not shown the sheets in the selected file.
And the “OK” button was greyed out.

[mariosv]

I think “Link to external Data” needs named ranges for select, if nothing is selected the “Ok” button is disable.

To link sheets in other spreadsheets the options are through:
Menu/Insert/Sheet
Menu/Insert/Sheet from file.
In both you can tick the link option, so you can update the data later.

There are several places to set up how data are updating:
Menu/Tools/Options/LIbreOffice calc/General - Updating.
Menu/Tools/Options/LibreOffice calc/Formula - Recalculation on file load.

The place to update data or modify the link:
Menu/Edit/Links.

Also it’s possible past links through Menu/Edit/Past special [Ctrl+Shift+V], with both files opened.

[AlwynWellington]

Thank you and I have followed your notes.

In each case the “search engine” book gets a copy of each sheet in the “database” book.
In my experimenting this means the “search engine” book increases in size
by the same extent as the “database” book.
So there is no apparent usefulness in keeping the “database” separate.
My objective is to use the “search engine” and the “database” on an android tablet using “AndOpenOffice” or similar.
My experience so far on that platform is that files should not be big and should not have (m)any external links.
My hope was that the ODS apps would emulate link references as in MS Excel.

I continue to hope for that.

And I would appreciate additional solutions.

Alan

[mariosv]

Please read the last paragraph, this create a link by reference. Or with both files open, you can create a formula referencing a cell in the other as you can do in the same file.