Is there a way to reference the entire sheet in a formula?
Excel has “Table1[#All]”.
Does anything like this exist in Calc?
Are you sure it is the entire sheet?, I think it refers to a defined table, in calc on Menu/Data/Define range.
FIXED Bug 85063 - EDITING: Excel structured references returns error 508 on Calc
Yes, it seems it is a table. I don’t think Calc has this. My approach is to find the used area of a named range or a cell range an work with that.
Bug 132780 (FormatAsTable) - Feature Request: Add Calc Tables with functionality similar to Excel’s “Format as tables”
Thanks. I needed to find only the starting and ending cells with content within a range.
So if the range is “A1:R44” and only “A3:Q22” has data then I needed to find that used range.
I just implement find_used_range into OooDev a few minutes ago.
Note that for an entire table (i.e. database range, not sheet) you can simply write its name Table1.
Recent versions support database ranges which store their SortDescriptors, FilterDescriptors and various properties, such as “ContainsHeader”.
INDEX(Import1;1;0) references the header row.
OFFSET(Import1;1;0;ROWS(Import1)-1) references the data below header row.
INDEX(Import1;2;1) references the first cell below header row.
INDEX(Import1;ROWS(Import1);COLUMNS(Import1) references the bottom-right corner cell.
1 Like
And while we are in it, Base can represent Calc “tables”, so they can be queried by plain, simple SQL. A rectangle of Calc cells qualifies as a pseudo-table if
- the table is a normalized one that qualifies for a pivot table according to Database normalization description - Microsoft 365 Apps | Microsoft Learn
- the table represents some sheet’s one and only content
- if there is other content on the same sheet, you can isolate the table as a database range (menu:Data>Define…)
After connecting a Base document to the Calc document, Base lists all used areas of all sheets and all database ranges as tables of a pseudo-database. menu:Tools>Table Filter… can be used to hide inadequate spreadsheet ranges, that do not qualify as normalized tables.
What you can do with it:
- First of all, serial letters. Most Base documents are generated by the mail merge wizard clandestinely, which causes a lot of confusion among users.
- A query returns an arbitrary subset of rows and columns from a table in arbitrary order of rows and columns:
SELECT "Column D", "Column A"
FROM "Sheet1"
WHERE "Column B" > 0
ORDER BY "Column F" DESC
This is rather self explaining and yes, it is some way to pass an entire sheet to some kind of “formula” as you stated your question.
- A table, or any subset of rows and columns of it, can be linked to a linked database range (UNO DatabaseRange with ImportDescriptor).
- A table, or any subset of rows and columns of it, can be used as source of a pivot table.
- Base’s report builder is a powerful tool set to dump table data (or abitrary subsets) into a sophisticated text layout.
- You can create linked database ranges from any table (or arbitrary subsets), even within the source document. Formula ranges adjacent to the linked do adjust automatically to the size of the imported data. For instance, you can have your source table on Sheet1, filter/sort that table by means of a Base query, dump the filtered/sorted result on Sheet2 , complemented by some spreadsheet formulas next to (and adjusting to) the import range.
- You can create pivot tables from cell ranges in other Calc documents, which is impossible to do otherwise.
- If you don’t like to distort your precious source data with subtotal ranges (menu:Data>Subtotals…), just take a detour via Base and apply the subtotals to the linked database range on another sheet.
- All this used to work since OpenOffice 1.0.
- Coding is as simple as:
ThisComponent.DatabaseRanges("Import1").refresh()without bothering about range sizes, without dragging down formulas.
Limitations
- Base can not “convert” your spreadsheet into a database. It can not turn water into wine.
- Your pseudo-database linked to a spreadsheet document is read-only. You have a spreadsheet application at hand to edit a spreadsheet.
- After doing so, you need to restart the office in order to propagate the changes to the pseudo-database. May be a bug. I remember old versions of OOo refreshing the spreadsheet links if they were stored to disk.
- A query (SQL SELECT statement) can include only one table. The only possible aggregation is COUNT(*). There is a limited set of skalar functions for file based databases: https://www.openoffice.org/dba/specifications/file_based_functions.html
When linking the query results back to Calc, pivot tables can do the aggregations (SUM, MIN, MAX, AVG etc for grouped elements) and you have all the sheet functions at hand.
1 Like
Ctrl+A says A1:XFD1048576
The database range in LibreOffice corresponds to the table in Excel. When you use the database range name in a formula, then you get the entire range. It is the other way round, that LibreOffice has no way to get the data or the header only, that is something similar than “Table1[#Data]” or “Table1[#Header]” does not exist in LibreOffice.
You get the top-left cell with function CELL(“ADDRESS”;…). You get the number of rows with function ROWS(…) and the number columns with function COLUMNS(…).
The attached file has a database range “myData”.
DatabaseName.ods (22.7 KB)
Depending on your purpose a named range or labels might work better.
1 Like
It does. If a Table1 database range is defined, one can perfectly write Table1[[#Data]] (or other table structured references) (note the #All, #Header and #Data items need extra [] brackets just like all other items, the simplified form omitting them is not implemented) in a formula expression, but it is not stored as such in a .ods document because I never got around to actually submit an ODFF standardization request. Stored in document instead is the corresponding cell range reference.
1 Like
Sorry @erAck, it does not work for me, tested with version 25.2. If my database range has the name myData, then =SORT(myData[[#Data]];4;-1) for example, results in Error in parameter list (Err 504). =SORT(myData;4;-1) works, but does not exclude the headers from sorting.
=myData results in a table including headers, whereas =myData[#Data] results in Error in backeting and =myData[[#Data]] results in Invalid Name.
Please try it with the uploaded file myDatabase.ods (24.7 KB) and if it works for you please write as text into a cell which formula I need to use.
BTW, SORT(myData;4;-1) means automatically SORT(myData[#Data];4;-1) in Excel.
Both =SORT(myData[[#Data]];4;-1) and =myData[[#Data]] work for me, 24.8, see attached.
myDatabase.ods (20.2 KB)
A plain myData not excluding headers may be a bug, but long standing behaviour.
Hi @erAck, tested again: a master build 2dce213c does not work, a master build 69fc5c39 works. It seems I need to update my LibreOffice versions.
Addition: It is not the LibreOffice version, but the user profile. With a fresh user profile it works, with my inherited user profile it fails.
I have found the reason: The failure happens, if registrimodifications.xcu contains
<item oor:path="/org.openoffice.Office.Calc/Formula/Syntax"> <prop oor:name="EnglishFunctionName" oor:op="fuse"> <value>true</value> </prop> </item>
If it has value false the expression myData[[#Data]] works.
=SORT(myData;4;-1)
does not work on Excel.
myDatabase.odg screenshot LO vs Excel (209.4 KB)
and with =SORT(SourceData.$B$8:$E$17;4;-1) Excel also does not exclude the headers from sorting as LibreOffice.
That’s odd indeed and appears to be a bug, the English table item keywords should work as well.
I tried a German UI switched to EnglishFunctionName and got #NAME? with =myData[[#Data]] and Fehler:504 with =SORT(myData[[#Data]];4;-1). In both cases [#Data] is lower-cased to [#data] so not recognized as valid identifier. The German identifier like in myData[[#Daten]] doesn’t work either. Switching off EnglishFunctionName again it does. The same happens in an English UI, switching to EnglishFunctionName apparently discards table item keywords.
An explicit cell range des not have table or database range properties.
I have written bug report tdf#162087 for the bug and enhancement request tdf#162088 for extending ODF.
1 Like