How to display and link internal data?

English
#1

[Really just need to know the correct libreoffice calc lingo / search terms …]

LibreOffice 5.4.7.2 (x64)
[Last version that kept help internal - e.g. F1, vlookup() gives referential text immediately. No, I’m not going to change versions away from such functionality.]

What are the steps to have different views of the same data?
- in Base, this would be a query, but this is within Calc.
(‘Query’, ‘View’, ‘Filter’, ‘Data’ seem to have so many connotations in libreoffice, I’ve been unable to find the answer to this via help or web searching.)

(1) How to link to internal data.
- I can link to external data, using the current spreadsheet filename.
- barring a way to link to internal data, is there a ‘tag’ for this spreadsheet, e.g. ‘self’ or some other equivalent to ‘localhost’?

(2) How to display that data sorted differently?
- for that matter, how to specify a subset of that data?

I have a spreadsheet. Consider, for example, a table of colour names, and HTML, RGB, and CMYK values.

I am constantly resorting this named ranged based upon what I know and am looking for, at that time. e.g. I might look for ‘Dark Orange’ to get the RGB values - or the reverse, look for #ffa500 to find the ‘name’.

So I create a spreadsheet tab / named range of ‘Colour_Name’, then 3 more tabs, HTML, RGB, CMYK.
- how do I auto-populate HTML, RGB, CMYK, based (linked) upon the ‘Colour Name’ ranged name? [Being simple ‘=Colour_Name’ references, but sorted by different columns.]
- for external data, I would have to refresh to pick up any changes. I can live with that, but would prefer some sort of auto-refresh if available.

Really, just need the appropriate libreoffice help search terms. At the moment, I’m drowning in non-answers in my web searching to date.

TIA.

I can see the help for ‘named ranges’ but, sadly, no help for inserting (viewing) that named data is present in the help. Nor corresponding related topics listed.

#2

In Calc I would use filter for your purpose, because a filter can output the filtered data to another place.
However your idea would be a bit more complicated, as a filter can only output to one destination. You could place the source of the next filter at the destination of the first to chain “data views”, but that is not very flexible.
.
So maybe some macros to create sorted views may be easier.
.
(You already seem to know using Base for this is the best suggestion…)
.
On your other topic:

Do you mean hyperlink() or DDE-Links or direct references?
In general: When you type a “destination-cell” in a formula, try to point to the cell with the mouse instead and Calc should insert a correct reference.

I’d say usually you just omit any external reference.
So instead of filename#sheet1.A1 you use sheet1.A1 for “this” file and A1 for “this sheet”.

PS: Out of curiosiy

You mean the time to open your browser or to retrieve something from the internet? (Actually I use google more than actual help-system.)

1 Like
#3

If your data are normalized, the easiest way goes like this:
File>New>Database…
Connect to existing db of type “Spreadsheet”
Yes, register the database.
Save the database document.
Now you have a database view on your spreadsheet data. Nothing has been imported nor converted by any means. Your data are still in the spreadsheet.
Create as many queries as you need. Since a spreadsheet does not provide database structures, a query can filter/sort only one table.
Parameter queries are supported: SELECT * FROM "Sheet" WHERE "Value" >= :Enter_Minimum
You can drag query icons from Calc’s data source window (Ctrl+Shift+F4) into the sheets, even sheets of the same document that constitutes the data source.

I forgot one issue:

  • Calc menu:Data>Define…
  • In the dialog select a linked database range, usually named “ImportX”.
  • Click “More Options” and always check “Keep formatting” (the spreadsheet’s formatting) and “Insert/Remove Cells” (when the size of the record set has changed). These options should be always on, they should not even exist.

Call menu:Data>Refresh while the cell cursor is within the imported range in order to re-query.
Formulas adjacent to the db-range adjust automatically when you refresh the database range, for instance =$A2*$X$1 will be copied or removed along the adjacent db-range.

1 Like
#4

the easiest way goes like this: …

… D’OH! :man_facepalming:

Simple. Elegant. Straightforward. Thank you!

Actually, as occurred to me later … an even simpler answer …

Copy the spreadsheet tab as many times as needed, sort each tab in the desired way, e.g. name, RGB, CMYK, whatever. Get on with your day.

Now, that only works for this very simple use case - relatively static, limited, data (rows), and permutations. [And even allows vlookup()s elsewhere as needed.]

Otherwise, @Villeroy’s answer makes the most sense.

And … having gotten there … it’s only a small further step to simply have the data in base in the first place. (Smaller bites of the learning curve at the time.)


When I first started ‘web searching’ for answers to this (‘internal data’), I immediately came across overwhelming hits of ‘split database’ (Separate data from ‘front end’.), and / on LO Base solutions.

As I read further, the (potential) (corruption?) issues seem to surround use within a multi-user / computer / enterprise. (i.e. different CPUs working with the data).

For this single-user, albeit, perhaps across multiple computers (main, laptop, etc.), there feels like no need to ‘split’ the database. Have I understood this correctly?

#5

> I can link to external data, using the current spreadsheet filename
Do you mean hyperlink() or DDE-Links or direct references?

‘Internal’ would be direct cell references (named ranges)
[I realized, afterwards, is what they would be.]
(And, given Villeroy’s answer, Calc has no such beastie. [Internal.])

External would be other Calc files.

LO_LinkToExternalData

In Calc I would use filter for your purpose, because a filter can output the filtered data to another place.

Each ‘view’ would be the entire original table, sorted by a different column. Filters can sort?

You mean the time to open your browser or to retrieve something from the internet? (Actually I use google more than actual help-system.)

Different purposes at the time. If I’m looking up documentation about a topic, sure the browser works fastest.

But if just looking for a function reference, nothing beats the ‘inline’ help. e.g. = v l o o k u p ( F1, v l <double-click> - and it’s there.

No browsers (which one), Ctrl-Win switching (which desktop), no searching for where it popped up this time, no product selection, version selection, search term retyping … it’s ‘just’ ‘there’. Click the X to close, get on with the thought I’m in the middle of.

Agreed, documentation / learning, vs. ‘library’ ‘function’ ‘reference’. Different purposes.

#6

There are no less than 5 different ways linking to external data. The database document is what virtually everybody wants (working with record sets), but nobody uses (somewhat obscure).
Link to external data, URL-Formulas, DDE-Formulas have one common issue. They do not expand and shrink with the record set because they have no notion of record sets.

#7

It’s further complicated by the (online) help mixing products so invisibly.

So, for example, I came across sort( <range>, <column>, <etc.>) at one point … to lose time finding out there is no sort() in calc, it’s a base function.

I appreciate your point, record sets being what people want, but calc doesn’t ‘understand’ (ergo, need to data → refresh), but coming out of a spreadsheet, record sets isn’t an immediate leap (of comprehension of the exercise at hand).

Mind you … a calc sort() of the nature above doesn’t seem like a great leap of functionality.
- until you consider not being on that tab when the data on the source tab changes, need to refresh, and all the other ramifications of it. At that point, the mind just boggles as to the overwhelming complexity of programming in the concept.

#8

No, this you misread completely.

  • Server databases handle Multiuser-setup without problems
  • The problems arise with embedded databases, because they are stored compressed in the. .odb-file. For access they are extracted from there and on close need to be packed again in the file. A crash or power-loss during compression may result in a corrupt file.
  • Splitting the .odb and the actual database adds an additional layer of security. It does not matter if the databse is a multi-user postgres or a singe user sqlite-database.
#9

FWIW…

I do not recall which features are already included in LO 5.4. I am posting these instructions in some “generic” way, in case someone finds this useful.

When reading the purpose and conditions for this particular case in Calc, I would use the simpler AutoFilter feature, selecting from the headers of the table (including them) and up to the last row of data); then menu Data > Autofilter. With AutoFilter, there is no copy of the results to some other area; it’s “in place”.

Using AutoFilter, you could potentially apply some sort, but you don’t really need to. Just open the dropdown arrow for the column that you need to filter (i.e. the data that you know), type-in the data in the Search box within that column’s filter, and OK.

The table will show relevant rows according to the filter; use the resulting data (displayed on the other columns) as you need, and then open the same dropdown arrow as before to select “All” in the filter (i.e. show all the rows, instead of limiting them to the filter). or simply close the file without saving it.

#10

Calc can sort in place, which may spoil formulas with positional references, and Calc can dump sorted results to some other range. However, this does not come close to database queries where you can dump any collection of columns in any order of columns, optionally with adjusted column labels, with rows ordered by any combination of columns and store each sorting/filtering instruction under a descriptive name without ever touching the original data set in the table.
Most spreadsheet connections connect some list on a spreadsheet with serial letters in Writer. Interpreting these address lists as normalized database tables (hoping they are normalized actually) makes it possible to replace any list with any other list by writing a simple query without having to adjust the column order, column labels nor the placeholder fields in the Writer template.

#11

There is no calc sort() function I am aware of.

Herein, ‘a calc sort() of the nature above’ refers to the base sort() function.

So, for example, I came across sort( , , <etc.>) at one point … to lose time finding out there is no sort() in calc, it’s a base function.

#12

@ Wanderer:
No, this you misread completely.

  • Server databases handle Multiuser-setup without problems
  • The problems arise with embedded databases, because they are stored compressed in the. .odb-file. For access they are extracted from there and on close need to be packed again in the file. A crash or power-loss during compression may result in a corrupt file.
  • Splitting the .odb and the actual database adds an additional layer of security. It does not matter if the databse is a multi-user postgres or a singe user sqlite-database.

Sorry, you entirely missed the context.

For this single-user, albeit, perhaps across multiple computers (main, laptop, etc.), there feels like no need to ‘split’ the database. Have I understood this correctly?

Inherently, this context is a Base embedded HSQLDB DB, accessed on the local net (only), directly, just like any regular .ods, .odt, .od<whatever> through one’s regular network file sharing. (But, like said, single-user - I can only sit in front of one computer at a time, as it were.)

No offense intended, but it doesn’t seem like your answer addresses the question posed. FWIW.

Herein, Villeroy noted to put the query in Base and call it from calc.

I noted that at that point, almost may as well just move the data into Base in the first place.

Ergo, the question posed.

#13

menu:Data>Sort …
Before calling that command, you can define the list as a named database range (unlinked to any database). This object is able to store sort parameters defined in the dialog. The sort dialog’s extra options allow dumping the sorted record set to some target cell.
menu:Data>Refresh will dump a new sorted record set to the target cell.

Then there are several newly introduced array functions, which do nor work correctly yet, such as SORT function

#14

Kewl!

Thank you for letting me (us all) know! Good stuff.

#15

Only one sort order per db-range is stored. In a database document you can store as many queries as needed (even more than that).

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.