Log In
Register
Home
December 8, 2017
Offline
Hello Friend;
Good morning
In attached 2 worksheets - workbook below on Piv. Tab. & getpivotdata worksheet I tried to 1)sort dates in A to Z order , but
having difficulty 2) like to create grand total field for each salesperson, tried by clicking on Design Tab , In Layout Group , click on Grand
Totals & On for rows, but, it does not do anything. (I MUST be doing something wrong), 3) For Feb. 2015 , for salesperson - John, and
Salespersons - Kevin Pete are in 2 places , Can you Please advise me what I did wrong on adjacent worksheet which is data for this Pivot. Table ?
Thank you very much.
Have a great day.
VIP
Trusted Members
December 7, 2016
Offline
Hello Mitul,
Sorry to say, but your dates are in a mess. Some "dates" are typed in so Excel does not recognize them as dates, but as text. I have used conditional formatting to highlight these text dates in red. An easy fix is to just remove the dot and Excel will recognize the data as dates.
By repairing this your issues 1) and 3) gets resolved without any other actions than to refresh your Pivot Table (might be that you need to set the number format again to have the dates showing like Apr-15).
For your issue 2), you need rearrange your data if you want to have a grand total per salesperson.
See attached workbook Piv.-Tab.-Getpivotdata-from-ExcelFunctions.net_CondFormat.xlsx to see the conditional formatting showing which "dates" that are actually text and not dates.
See attached workbook Piv.-Tab.-Getpivotdata-from-ExcelFunctions.net_Fixed.xlsx for a sample showing corrected dates which solves your issues 1 and 3, and for your issue 2, see the last two worksheets for the rearranged data.
I hope you understand what I try to show.
Br,
Anders
December 8, 2017
Offline
Hello Anders; (my friend)
Good morning
How are you?
Thank you for responding to my Pivot Table problems.
On ExcelFunctions.net DATA worksheet in cells A10:A12, it was maj/15, changed it to mar/15, (but, on Piv. Tab. & GETPIVOTDATA
worksheet), in cell E29, after refreshing & changing number format to Custom - mmm-yy, it stilll says maj/15 Total
Exact same issues on ExcelFunctions.net DATA worksheet cells - A31 : A33, A52 : A54, A67 : A69 A70: A72 , A79 : A81
Exact same issues on Piv. Tab. & GETPIVOTDATA worksheet Cell E54, E89, E114, E119, E134 (after refreshing & changing number format
to Custom - mmm-yy, it does not change
Please advise & correct those problem.
Also, on Rearranged Pivot worksheet, how did you get Grand Total column?
Thank you very much.
Have a great day.
Sincerely;
Mitul.
VIP
Trusted Members
December 7, 2016
Offline
Hello Mitul,
I have worked with your sample file in Excel for Mac, so the approach is a little bit different than it needs to be when done in Windows version, but the result is the same. See the attached file for corrections and comments.
Br,
Anders
December 8, 2017
Offline
Hello Anders;
This is Mitul.
Thank you very much, friend. I appreciate .
VIP
Trusted Members
December 7, 2016
Offline
Hello Mitul,
I hope you get the picture of what I try to show, that you get the errors due to irregularities in your data. As I see it, you have grasped how to use PivotTable, how to get information from it and structure it, but the errors in the data source messes things up.
Keep struggle on, it is the only way forward. 😉
Br,
Anders Sehlstedt
1 Guest(s)
