I currently have a few columns working off of a pivot. The pivot has a missing dimension under one year, and the calculation is already fairly complex. What is the easiest way to add a double check for a missing Tender to Ship Group"?
=+IF($A29="Grand Total",SUM(AA$11:AA28),
IF($A29="","",
IFERROR(GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2017,"Tender To Ship Group",AA$10)-GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2018,"Tender To Ship Group",AA$10),"")))
This works great if the group shows up under both 2017 ad 2018, but I need to build in a check for missing and substitution such as
IFERROR(GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2018,"Tender To Ship Group",AA$10),GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2017,"Tender To Ship Group",AA$10))
=+IF($A29="Grand Total",SUM(AA$11:AA28),
IF($A29="","",
IFERROR(GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2017,"Tender To Ship Group",AA$10)-GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2018,"Tender To Ship Group",AA$10),"")))
This works great if the group shows up under both 2017 ad 2018, but I need to build in a check for missing and substitution such as
IFERROR(GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2018,"Tender To Ship Group",AA$10),GETPIVOTDATA("Loadnum",$A$8,"Customer Name",$A29,"City",$B29,"St",$C29,"Year",2017,"Tender To Ship Group",AA$10))
