Does Calc now support structured reference in database tables (such as Excel table formulas) ?

SebP · January 31, 2017, 9:58pm

I am referring to Excel tables created with CTRL-T and with explicit formulas (ex: =”@[apple]+[@pear]” with ‘apple’ and ‘pear’ two other column headers), rather than the usual “=N10+M10”.

Alternatively, is there another method to implement explicit formulas in large table?
Thanks,

mikekaganski · January 31, 2017, 10:12pm

Possibly that could be of some help:

https://help.libreoffice.org/Calc/Recognizing_Names_as_Addressing
https://help.libreoffice.org/Calc/Naming_Cells
https://help.libreoffice.org/Calc/Define_Names

SebP · January 31, 2017, 10:31pm

Thanks for the quick feedback. I have reviewed the documentation but could not find an easy way to achieve the same explicit formulas than with excel table (https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e)

SebP · January 31, 2017, 10:33pm

I found Eike Rathke 2015 presentation discussing the implementation of this feature: http://erack.org/material/2015-09-25_LibreOffice-Conference-Aarhus_Implementation-of-Table-Structured-References-in-LibreOffice-Calc.pdf
But I could not make it work and suspect this is not fully implemented (I am using LibreOffice version 5.2.4.2).

mikekaganski · January 31, 2017, 10:52pm

Do you have an Excel file with such references? You may check if it’s supported, by opening it with Calc. If it is, then you’ll see the syntax in the formulas. If not, then there will be an error.

SebP · February 2, 2017, 3:25pm

Hello,
In tried the experiment with an excel file and it is loading ok. However, after savins as .ods, the structured formulas are replaced by standard ‘A1’ formulas.

eeigor · February 9, 2020, 9:13am

It works as following:

NamedDatabaseRange[FieldName]
NamedDatabaseRange[[FieldName1]:[FieldName3]]
Operator “@” doesn’t work (ref to table cell in current row), but you can use “implicit intersection” and define the whole field range as NamedDatabaseRange[FieldName]. The current row cell will be used implicitly. That’s the same.

Note: Syntax as NamedDatabaseRange[[#This Row],[FieldName]] also works in my localized (russian) version of Calc (“This row” means “Эта строка” and semicolon is used instead of comma as a list separator).

For example: NamedDatabaseRange[[#Эта строка];[FieldName]]

See for details:
https://www.youtube.com/watch?v=qyukaIetjPU
time: 06:30

Unfortunately there is no mention about it in Calc 6.2 Guide. But if you save, close your file and then open it again, all structured references will be replaced by common references to ranges. There is a problem!