Forum Discussion
GETPIVOTDATA #REF error.
Good morning,
I am using the following and it keeps giving me a #REF when one of the items is not located in the pivot table for that particular location. What can I do to still get the other locations data without typing over the formula?
=F8*(GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS","Location","Bolivar")+GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS","Location","Bolivar"))
Any help would be greatly appreciated.
You may enter locations in some cells, e.g. M8, M9, etc. If in M8 is Bolivar the formula could be
=F8*( GETPIVOTDATA( "Sum of Quantity", $A$3, "Charge", "BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS", "Location",$M8) + GETPIVOTDATA("Sum of Quantity", $A$3, "Charge", "BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS", "Location", $M8) )and drag it down. But all depends on how your data is structured.
Thank you. Under Bolivar you have no data for the field
"BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS"thus GETPIVOTDATA returns an error if you reference above field.As variant you may wrap it by IFERROR=F8*( GETPIVOTDATA( "Sum of Quantity", $A$3, "Charge", "BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS", "Location",$M8) + IFERROR( GETPIVOTDATA("Sum of Quantity", $A$3, "Charge", "BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS", "Location", $M8), 0 ) )Where to keep locations depends on where result of the formula to be placed. In general if build PivotTable on data model and if you are on Excel 365 that could be returned the spill for all locations using CUBE formulae.
I figured it out:
=F8*(IFERROR(GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *PLUS*- Indv+Spouse<FREE MEDS<LABS<GBS","Location","Bolivar"),0)+(IFERROR(GETPIVOTDATA("Sum of Quantity",$A$3,"Charge","BT *Plus*- Indv+Children<FREE MEDS<LABS<GBS","Location","Bolivar"),0)))
Yes, thank you for the update
