Log In
Register
Home
December 8, 2017
Offline
Good morning , friend
How are you ?
In workbook attached below , in DATA FOR 2015, PIV. TAB. worksheet like to learn (If possible), Excel automatically change from North to South
for Jan, then, change automatically from Jan to Feb, and gives amounts for both North & South for Feb , then, change automatically from Feb to
Mar & gives amounts for both North & South.
Before few days , I also sent same post from Firefox browser, but I cannot find that post today. . So, I am sorry that I am sending again
today from Internet Explorer
Please help me out .
Thank you very much.
Have a great day.
July 16, 2010
Offline
Hi Mitul,
There's no easy way to write one formula you can copy down column K in the 'Data for 2015..' tab because the layout is not consistent i.e. one row has months and regions, then you have a total for the regions. Each of these requires a different syntax in the GETPIVOTDATA function. You can see this if you look at the formulas;
January North is; =GETPIVOTDATA("Sales",$J$15,"Region","North","Month","Jan")
And the Total Sales is: =GETPIVOTDATA("Sales",$J$15,"Month","Jan")
i.e. the first formula specifies a region, but the total sales doesn't.
Mynda
December 8, 2017
Offline
Hi; Mynda;
Thank you for your reply.
In "Data for 2015..' tab how can I make layout consistent? Can you please do it for me ?
July 16, 2010
Offline
Remove the totals, that makes it consistent. Then you can just modify the first formula for the whole column: =GETPIVOTDATA("Sales",$J$15,"Region","North","Month","Jan")
Reference the cells containing the region instead of the hard coded "North" etc.
Mynda
December 8, 2017
Offline
Hello Mynda;
Thank you for your reply.
In workbook attached below, in "Data for 2015.. " worksheet , Removed totals, that made it consistent.
Is there any way we can write GetPivotdata formula to have Excel change automatically from Region "North" to "South" for each and every month? and I had to change reference for ROW function from January - ROW (A1) to February ROW (A2). Is there any way to have Excel do it automatically?
I like to master GetPivotData formula .
Please help me.
Thank you very much.
Have a great day.
Sincerely;
Mitul.
July 16, 2010
Offline
Hi Mitul,
Your data structure is all wrong for copying a formula down a column. I recommend you read this tutorial on how to write formulas efficiently.
In the attached file I fixed the first 3 months and you can see the formula simply references the cell containing the region to pick up the region information.
Your source data doesn't contain dates, the months are entered as text. So you can't use ROW or COLUMN to automatically generate the month number because the PivotTable doesn't have any months/dates in it, only text values that represent months. So, for the month, I extracted the first 3 characters from the month name in column I.
Please read this tutorial on how Excel works with dates.
Mynda
1 Guest(s)
