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

Getting Data from PIVOT

X

Xayn Alam

New Member
Hello, I am using below formula in order to get the data from PIVOT but the thing is it is getting more bigger and bigger as I have to add the numbers of all the months. Can someone please suggest a smart way of doing so?

=IFERROR(SUM(GETPIVOTDATA(T(P$6),Pivot_EBTDA!$B$5,"Substream Leader (approves IL3 to IL4)",$B$42,"Business/Operational Unit",C5,"Initiative Execution Owner (Accountable)",D5,"Metric > Metric",$N$7,"Metric > Metric",$N$8,"Metric > Metric",$N$9,"Metric > Metric",$N$10,"Metric > Metric",$N$11,"Metric > Metric",$N$12,"Metric > Metric",$N$13,"Metric > Metric",$N$14,"Purpose",$K$7)+GETPIVOTDATA(T(P$7),Pivot_EBTDA!$B$5,"Substream Leader (approves IL3 to IL4)",$B$42,"Business/Operational Unit",C5,"Initiative Execution Owner (Accountable)",D5,"Metric > Metric",$N$7,"Metric > Metric",$N$8,"Metric > Metric",$N$9,"Metric > Metric",$N$10,"Metric > Metric",$N$11,"Metric > Metric",$N$12,"Metric > Metric",$N$13,"Metric > Metric",$N$14,"Purpose",$K$7)+GETPIVOTDATA(T(P$8),Pivot_EBTDA!$B$5,"Substream Leader (approves IL3 to IL4)",$B$42,"Business/Operational Unit",C5,"Initiative Execution Owner (Accountable)",D5,"Metric > Metric",$N$7,"Metric > Metric",$N$8,"Metric > Metric",$N$9,"Metric > Metric",$N$10,"Metric > Metric",$N$11,"Metric > Metric",$N$12,"Metric > Metric",$N$13,"Metric > Metric",$N$14,"Purpose",$K$7)),0)
 
The parameters which I have to consider are as follows:
  • Column B contains the divisions and every division represents a name listed in B42:B45. Network Logistics SW refers to B42, Network Logistics NE to B45 and Flow & Shipping to B43:B44.
  • Column E "Impact" further divides in sub categories mentioned in column L8:L14. which means there are the cases where only few or more which are not listed here can be present in raw data but I have to pick only these.
  • Then in the raw data there are three categories Planned, Actual and Forecasted. In this case we only have to consider Actual which is mentioned in column I7.
  • In column N there are months of 2019 and I have to take January to YTD every time when I will create the report so it is another range which needs to be included.
  • Raw data in present in Raw Data and PIVOT was created from this data. So how it should be done in a smarter way?
Regards.
 

Attachments

  • Working File.xlsm
    941.3 KB · Views: 6
Xayn Alam
2) Explain. what do You want?
... Do You know that Yourself?
Why do You have that Pivot_EBTDA-sheet?
or
it should show at once Your needed results.

There weren't even 'any sample results'... hmm?
 
I want the sum of all the months as per the criteria I have shown in the text. Pivot was just to have visibility. I am looking for smarter way.
 
You must log in or register to reply here.
Back
Top