Pivot table totals update assist

DarrenK

Board Regular
Joined
Aug 5, 2017
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Good morning,

Apologies as I felt specifying the issue in the title would be too lengthy. here goes.

I have a pivot table. As new data is added and the table refreshes, the GRAND TOTAL row changes but also the column that the grand total is in can change. My issue with that is I have a formula (in I17 if it matters) that takes the grand total number from the pivot and multiplies it by .8. This is so that I know exactly how many rows to grab when I send an email to illustrate the top 80% of issues.

1) Is there a way to update the multiplication formula so that it automatically locates the GRAND TOTAL cell regardless where it appears on the page after the pivot refreshes? While annoying, it's not very time consuming to locate the new GRAND TOTAL cell manually. But it's tedious and I'd like to have Excel work for me in this instance.
2) The source data for the above pivot has a column titled 'date' and the information in that source sheet is formatted mm/dd/yyyy. There are many rows in the source data that have the same date, depending on how many reps work that day. When I pull up the filter for the pivot table to select the prior workday's date, it lists every day of the year line by line. Is there a way I can get the filter to look more like a checkbox list (months then days) versus the way it currently looks? See images below for reference. Unchecking yesterday's date and checking today's date feels faster than scrolling down the entire list. What am I forgetting to do?

1631633060930.png


1631632986637.png
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,143,685
Messages
5,720,286
Members
422,274
Latest member
steefq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top