• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set up Pivot references in a formula that are easier to read

L

loesje123

New Member
Hello,

I have a summation of several pivot table references and would like to find a way to read these easier.
this is how it looks. It is always from the same data field, same pivot table, same and same column just the fields are different. Does anybody have any solution?

=((GETPIVOTDATA("Sum of Length",pvt_onderwijs!$A$20,"Subject","BW_sani","Page Label",2)+GETPIVOTDATA("Sum of Length",pvt_onderwijs!$A$20,"Subject","BW_sani_bestaandeWanden","Page Label",2)+GETPIVOTDATA("Sum of Length",pvt_onderwijs!$A$20,"Subject","BW_LR_gesl","Page Label",2)+GETPIVOTDATA("Sum of Length",pvt_onderwijs!$A$20,"Subject","BW_MultZaal_gesl","Page Label",2))*2-GETPIVOTDATA("Sum of Length",pvt_onderwijs!$A$20,"Subject","BW_sani_tegelwerk","Page Label",2))
 
No really clear, but you could just store your field list etc in range and use cell reference instead of hard coded strings.

But personally, if you are using Excel 2010 or later, I'd load data to data model and create OLAP based pivot table.
Then use CUBE functions.

If you are not familiar with CUBE functions, you can create pivot table first, then convert pivot table into CUBE formula.
Go to PivotTable Analyze ribbon -> Calculations -> OLAP Tools -> Convert to Formula

You can read more detailed explanation about CUBE functions in links.
https://www.turtle.works/knowledge/dynamic-excel-cubevalue-and-cubemember-functions/

https://powerpivotpro.com/2010/01/pulling-back-the-curtain-intro-to-cube-formulas/
Use link at the bottom of the page to go to next article as well (CUBESET, CUBERANKEDMEMBER )
 
You must log in or register to reply here.
Back
Top