get a row label from pivot table

Copper Contributor

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...

 

 

9 Replies

@omdl2020 

What do you mean by "close the pivot table"?

@Hans Vogelaar The dropdown menu in the pivot table. In the example from Xia

@omdl2020 

That's how GETPIVOTDATA works. If you don't want that, it's better to use SUMIFS based on the source data. Enter A and B in M3 and M4.

Then enter the formula

=SUMIFS($E$2:$E$9,$A$2:$A$9,N$1,$C$2:$C$9,$M3,$D$2:$D$9,$M$2)

and copy or fill to the other cells. See the attached version.

 

Thank you for your reply.

But I need Xia and A and B from the pivot and now you typed it so it is not dynamic...

@omdl2020 

In that case, I'd create a second pivot table with Werkzaam as filter field. See the new version.

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 ;)

@omdl2020 

I don't think it's possible to do what you want, but hopefully someone else will prove me wrong.

@omdl2020 

GETPIVOTDATA() doesn't work in such case, it provides data if only it is visible in PivotTable. Alternatively you may work with cube assuming you are at least on Excel 2010, as I remember it's the first which supports data  model.

 

Creating PivotTable add data to data model by checking

image.png

Create PivotTable and after that convert it to cube formulas. Now you may take these formulas and convert it to form you need, for example

image.png

in H3 it could be

 

=CUBEVALUE(
   "ThisWorkbookDataModel",
    CUBEMEMBER("ThisWorkbookDataModel",
         "[Measures].[Sum of Aantal dagen]"
    ),
    CUBEMEMBER("ThisWorkbookDataModel",
       ($G$2,$G3)
    ),
    CUBEMEMBER("ThisWorkbookDataModel",
         "[Range].[Week]." &"[" & TEXT(H$1,"General") & "]"
    )
)

 

and drag it to the right and down.

Remove generated cube formulas you don't need.

 

If in parallel you need PivotTable as well, better to create it using data model

image.png

Filtering of this PivotTable won't affect cube formulas, they use only connection to data model but not another PivotTables created based on it.

@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.