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