Forum Discussion

omdl2020's avatar
omdl2020
Copper Contributor
Nov 13, 2020

get a row label from pivot table

Hi all!   I have a pivot table and I would like to get some information.   For Example the green sqare. When I close the pivot table the text will change...    
Formulas and Functions
omdl2020's avatar
omdl2020
Copper Contributor
Nov 13, 2020
Thank you but i don't need this information in a pivot table.
So the question is how to get a row label from pivot table in a cel that is not a pivot table 😉
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 13, 2020

omdl2020 

I am not a great Pivot Table user so I tend to duck out of the PT environment and resort to dynamic arrays.

= UNIQUE(Table1[Medewerker])

If you also wish to filter the headings to omit the rows without content the formula starts to get somewhat overcomplicated.

= LET(
  distinct, UNIQUE(Table1[Medewerker]),
  totaal,  GETPIVOTDATA(
    "Aantal dagen",PivotTable,
    "Medewerker", distinct,
    "Werkzaam",Werzaam),
  FILTER( distinct, ISNUMBER(totaal) ) )

In either case, the table content is of the form

= IFERROR( GETPIVOTDATA(
    "Aantal dagen", PivotTable,
    "Week", Week#,
    "Medewerker", Medewerker#,
    "Werkzaam", Werzaam),
  "-" )

 A combination of INDEX/SMALL/MATCH can be used in place of FILTER. 

Resources