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?
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?
