how to deal with getpivotdata returing #ref!
Posted on 2011-03-25
I have a pivot that shows data over time and includes historical data. A table that references that pivot shows the data for each month in the year, so, when the current year is selected, some cell have a #ref! because that month hasn't occurred yet and there's no data in the pivot, or, historically we don't have all the data for a previous year.
We need to be compatible with 2003, so, iserror is out and we also need to chart and do formulas based on the table (that get it's data from the pivot).
I also would like it so that if I sum 3 months (for a quarter) that all have no data, the result would be blank and not 0.
I did try =if(isref(getpivotdata(xxx), getpivotdata(xxx),"")
but, summing 3 cells like that, that are "", returns 0 and I'd rather have the result be blank, and, that formula seems rather long.