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.
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.
Lucy.
lucykennedy66 Glad I could help!