Language agnostic MATCH? Pivot with different locales

GfE · January 2, 2023, 1:03am

MATCH("Total Result";$'Pivot Table_Sheet1_1'.$4:$4;0)
correctly returns the last column of a pivot table as long as the locale (or “function names”?) is set to english. However, this attempt fails in other LO instances where, due to different locale, the “Total Result” column heading is named differently, e. g. “Summe Ergebnis”.

How would you make this work accross languages? As the spreadsheet is interacted with by international people, I don’t want to enumerate all possible translations of the string “Total Result” but rather seek a robust, language-agnostic approach.

Three ideas come to mind:

(a) match the “Total Result” cell by its internal (english) name, regardless of the user’s locale
(b) find the last column of the pivot table by pivot dimensions
(c - least elegant, but this would still be a solution:) fix the locale at document/file level, so it overrides the user’s locale.

Is anything like that feasible?
(LO versions vary a bit amongst users, but all are >= 7.3.)

Many thanks in advance for every helpful hint!

Villeroy · January 2, 2023, 12:45pm

Function GETPIVOTDATA should help here. getpivotdata.ods (57.3 KB)

erAck · January 2, 2023, 2:24pm

It’s not the locale, it’s the UI language the pivot table was last generated with. And as such that is not stable anyway, even in the same UI language different versions may have different terms or translations.
As Villeroy pointed out, the GETPIVOTDATA() function provides the necessary means.
See also Spreadsheet Functions .

GfE · January 3, 2023, 12:40am

Thank you both so much for the pointer!

It took me some rethinking the algorithm to make use of GETPIVOTDATA(), but doing so, I have found a working solution by now.

My previous code fragment was roughly equivalent to:

=IFERROR(VLOOKUP(A5;$'Pivot Table_Sheet1_1'.$A$5:$Z$1000; MATCH("Total Result";'Pivot Table_Sheet1_1'.$4:$4;0);0);0)*D5

whilst my new code works more robustly, independently from UI language, and supposedly also more efficiently:

=IFERROR(GETPIVOTDATA("Values";$'Pivot Table_Sheet1_1'.$A$2;"Key1";A5);0)*D5