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