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 lo...
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.
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
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.
