Calc: ? features to simplify analyses across multiple sheets / files?

[clear]

Perhaps Calc has a concatenation feature to define a ‘virtual’ sheet. For example virtual1 = sheet4 + sheet7 + file2.sheet5.

Perhaps Calc has a feature analogous Writer’s ability to inserting a table derived from an ods file. For example Import Range in analogy to Insert Row.

Perhaps Calc has a feature to define a virtual column. For example virtual1.A = sheet8.G + sheet3.G1:G36 + sheet5.D7:D128 + file3.sheet1.D … with analyses being performed on virtual1.A

Being relatively new to spreadsheets and Calc, I do not know the possibilities, if any, in Calc.

The main criteria is having changes in the data in the sources propagate into the analyses.

The closest and only ASK thread I found is from 2018

How to append new data to existing data in a Calc spreadsheet

(New Calc user here) I have an existing Calc spreadsheet (3 columns, many rows) and need to append new data (3 columns, many rows) to the existing data (it is contiguous in date/time). The new data is in csv fomat, identical to the format of the original data. How do I append the new data beginning at row N+1 of the existing spreadsheet? All of my attempts so far have placed the new data in cell (1,1) of the existing spreadsheet.

[Villeroy]

https://help.libreoffice.org/25.8/en-US/text/scalc/guide/consolidate.html?DbPAR=CALC#bm_id3150791
I never used this in 25 years, too complicated and prone to errors.

[clear]

Consolidate is not helpful RE this thread, based on experiments with it a few days ago. It looked promising, but I couldn’t figure out how to un-hide / show the
.

… rows, which are automatically ordered and then hidden from view …
.
or work with them using things like SUMIFS
.
That is as far as I took it as it no longer seemed promising.

[clear]

Given my limited knowledge, the best I imagine at this point is to create named ranges for the columns in the sheets. For example define <store>_ItemPrice for columns named ItemPrice in sheets named AceHardware, Lowes, … Then
.
SUM(AceHardware_ItemPrice, Lowes_ItemPrice, HD_ItemPrice, TrueValue_ItemPrice, Menards_ItemPrice, ...)
.
I haven’t tried that so don’t know it would actually work.
.
but even that approach, cumbersome as it is even in that simple SUM, is not directly adaptable to SUMIFS. It seems I’d need to define a bunch of SUMIFS for ItemPrice and then use them as arguments to SUM. For example define HD_ItemPrice_Tool_Total to be =SUMIFS(HD_ItemPrice, HD_ItemClass, "Tool")
.
In my mind, that would be way too onerous if it needed to done through the Calc GUI, but perhaps feasible if Calc could import definitions from .txt.

So I’m left with wondering if the situation has already been address, for example with features similar to what I posted above in the Q.

[CRDF]

[Only Sheet]
[other columns] | Suplier | ItemPrice | [other columns]
and Filter it.

[Villeroy]

Why all that complicated fuzz? Names are just alias names for formula tokens with relative, absolute or mixed range addresses. They are helpful when building spreadsheets. They are completely useless when building a database on sheet. I gave you a complete sample with data and pivot tables aggregating any combination of attributes without a single formula.
All you need to change is Tools>Options>Calc>>General>“Expand references…” so the program behaves as expected when inserting new rows below the current database range.

[clear]

@CRDF
What do you mean by [Only Sheet]?
If by [Only Sheet] you mean “create an all-in-one sheet, then delete individual supplier sheets” well, that’s not this, and that discussion is already in a different thread.

[CRDF]

@clear

and that discussion is already in a different thread.

Just to complement: “filtering” can be by Pivoting, of course.

[Lupp]

@clear

Perhaps Calc has a concatenation feature to define a ‘virtual’ sheet. For example virtual1 = sheet4 + sheet7 + file2.sheet5

I feel rather sure that I didn’t understand what you mean by the virtual1.A = pseudo-formula.
You may study the attached example wich contains UDF code I wrote once, mainly to prepare for charts. It probably is nearer to the post you quoted (How to append ...) than to the subject of your question.
disask_132015_collecting columns.ods (18.7 KB)

[clear]

@Lupp

@clear

Perhaps Calc has a concatenation feature to define a ‘virtual’ sheet. For example virtual1 = sheet4 + sheet7 + file2.sheet5

I feel rather sure that I didn’t understand what you mean by the virtual1.A = pseudo-formula.

I imagine a menu item Sheet > Insert Virtual Sheet ...
.
It would create a new sheet. The user would be presented with a pop-up with a field in which they could enter a valid sheet name and enter it’s definition e.g. sheet4 + sheet7 + file2.sheet5, where <sheetname> are internal and <filename>.<sheetname> are external. With either, ranges could be specified.
.
After clicking Ok, the new sheet would appear as a tab along the bottom, just as if a new conventional sheet had been created.
.
The appearance of a virtual sheet would be as if the sources had been copy/paste concatenated. Behind the scenes Sheet > Insert Virtual Sheet ... would concatenate based on column labels.
.
A virtual sheet would have many, but not all, of the same features as a conventional sheet. In particular, cell content would not be editable.
.
A virtual sheet would be dynamic, meaning changes in the content of source-sheet cells would propagate to the virtual sheet.
.
In virtual sheets, only their definition would be editable. Cell content etc would not be editable. Rows and columns could be deleted (the definition would update accordingly behind the scenes). New rows and columns could be created by editing source sheets, or by adding a source sheet to the definition.
.
The appearance of a virtual sheet could be modified. For example, rows and columns could be sorted/rearranged. Filters could be run to, for example, hide rows that do not meet given criteria, or highlight cells that do.
.
Once defined, one could use a virtual sheet in formulas, for example SUMIFS(Virtual1.D1:Virtual1.D100, Virtual1.A1:A100, <text>)
[/quote]

[Villeroy]

What you describe is a database query.

[Wanderer]

@Villeroy

database query.

Actually all @clear describes is database querycand or view…