Forum Discussion
Excel Dashboard
lucykennedy66 Rather than using SUM formulas that point to ranges inside pivot tables, make use of the GETPIVOTDATA function. Start by just selecting a cell and begin typing = and point to a column total, e.g. for "open".
It automatically creates the following formula:
=GETPIVOTDATA("Quantity",$A$3,"status","open")
meaning get the quantity from the pivot table that starts in A3 where the status equals "open".
when you now slice the pt to only show closed cases, you get a #REF! error,. because there no longer are open cases in this pt. Capture that by wrapping the entire formula in IFERROR like this:
=IFERROR(GETPIVOTDATA("Quantity",$A$3,"status","open"),"")
As a final step you can make it more dynamic by pointing a cell that contains the word open like in J9. Then you can drag the formula down, but be sure that the words J10 and J11 are spelled correctly.
I've done that in the attached file.
Without seeing the actual spreadsheet here, it's hard to give any advice. That's I'm sure why you've had no reply despite over 80 views.
Is it possible for you to post a copy of the spreadsheet--so long as nothing in it is confidential or proprietary? If you can' do it here, post it on OneDrive or Google Drive and post a link. The actual sheet is far FAR more helpful than an image so please do your best to post a spreadsheet that illustrates the phenomenon you're describing.
Thank you for replying I have attached an example here as the actual sheet is restricted to my Work laptop.
I have running totals on a dashboard that are reading from a sum created using the pivot table but the totals change when the pivot table is filtered. Hopefully, this example sheet is understandable.
The Worksheet is called example
