How to generate GETPIVOTDATA formulas

In Excel there is an option to “Generate GETPIVOTDATA()” formulas. When this option is in effect a new formula is created by selecting one of the cells in the pivot table. This automates the process of building the formula for that pivot table and that cell.

Does something like this exist in LO Calc?


Select cell L5 in the pivot sheet I posted yesterday.
Hit Ctrl+F2 for the formula wizard. It helps to analyse and compose any (nested) formula.

@Villeroy , that is somewhat helpful, but I am looking for something that actually generates the formula when retrieving a value from a pivot table. In Excel, if I were to select an empty cell in your example file, say, cell O12, and then if I begin to write a formula by typing “=” and then using the mouse selecting cell C5, and assuming the Pivot Table has the Excel option to Generate GETPIVOTDATA() enabled, Excel will generate the correct formula: “=GETPIVOTDATA(“Value”,$A$1,“Years”,2005,“Date”,“Jan”,“Person”,“Barry”)” and store in cell L8.

The LO Calc function wizard helps to manually write a formula with some hints on syntax/field names…but it does not actually generate the needed formula. This capability is actually very nice for retrieving a single value from a pivot table without needing to fully understand its structure. If you then substitute a cell pointer for the month “Jan” and “Barry” you can construct a new table in perhaps a more condensed form than the full pivot table.

See my addition to your file in cell range N11:Q14.
getpivotdata.ods (53.9 KB)

Just to be clear, I’d like to have just one cell’s formula autogenerated. Then I could modify that for pointers to different people or months and then replicate that formula across this small table that focuses on just a few points covered by the full table.

The thing I am looking for is the ability to autogenerate the formula by pointing to the pivot table…like what happens in Excel.

Thank You,

Use Excel.

Well, sure, I just wanted to use any feature in LO Calc that might exist. :grinning:

Sorry if I came across as demanding. I am very impressed at how nice it is to use LO Calc instead of Excel. I used Excel in my daily work life for >25 years. Now that I have converted 100% to LO Calc I am finding it to be very useful and comfortable.