Hi,
Back in 2004, Jeff Borden asked about using GetPivotData with variable
criteria output. Here's his original question: http://tinyurl.com/2saafw
I have exactly the same issue. Debra Dalgleish supplies a working
solution, which is great, but its complexity makes maintenance an
issue. I am asking this question again in case anyone has any other
ideas?

I have a pivot table with 5 column fields.

On a different sheet, I want the user to be able to enter between one
and 5 criteria values and to have one GetPivotData function that will
catch them all.

For example, a user could enter any one of the three following options
in the five criteria boxes:
c1    c2    c3    c4    c5
In    In    Out    Off    On
In    Out    Out
In

The problem is that the GETPIVOTDATA function returns '#REF!' because
nothing in the pivot table matches for c4 and c5 criteria of
"" (blank).

My first thought it is to write a custom VBA function, GetPivotDataEx
which first checks for blank criteria, and then calls GetPivotData
itself with the empty parameters removed. Using VBA is okay within our
organisation, but I would rather avoid it.

Thanks in advance!

Andy