GetPivotData with variable criteria input? (revisited!)

[AndyCotgreave]

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

 

[AndyCotgreave]

PS - I had originally hoped that the criteria would ALWAYS read from
left to right, in which case Debra's solution would at least work.

I now don't think that will be the case: there may be some gaps - ie
someone may fill in criterias 1, 3, and 5, leaving 2 and 4 blank.

If that's the case, the nested IFs won't work.

Any ideas?

 

[Roger Govier]

Hi Andy

I'm having trouble envisaging your layout.
If you want to mail me a copy of your file, I would be happy to take a look
and see if I can come up with a solution.
To mail direct
roger at technology4u dot co dot uk
Do the obvious with at and dot.

 

[AndyCotgreave]

Just to let any readers know, Roger came up with a very intelligent
alternative approach usign SUMPRODUCT.

 

[Bijoy Mathew]

I am facing pretty much the same issue & have been scratching my head for a few weeks now :-(

Care to share Roger's solution??

Thanks!
Mathew




AndyCotgreave wrote:

Just to let any readers know, Roger came up with a very intelligentalternative
05-Oct-07

Just to let any readers know, Roger came up with a very intelligent
alternative approach usign SUMPRODUCT.

Previous Posts In This Thread:

GetPivotData with variable criteria input? (revisited!)
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

PS - I had originally hoped that the criteria would ALWAYS read fromleft to
PS - I had originally hoped that the criteria would ALWAYS read from
left to right, in which case Debra's solution would at least work.

I now don't think that will be the case: there may be some gaps - ie
someone may fill in criterias 1, 3, and 5, leaving 2 and 4 blank.

If that's the case, the nested IFs won't work.

Any ideas?

Hi AndyI'm having trouble envisaging your layout.
Hi Andy

I'm having trouble envisaging your layout.
If you want to mail me a copy of your file, I would be happy to take a look
and see if I can come up with a solution.
To mail direct
roger at technology4u dot co dot uk
Do the obvious with at and dot.

--
Regards
Roger Govier




Just to let any readers know, Roger came up with a very intelligentalternative
Just to let any readers know, Roger came up with a very intelligent
alternative approach usign SUMPRODUCT.


Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Track Visitor Information With Custom HttpHandlers
http://www.eggheadcafe.com/tutorial...37-61e9a5dfb127/aspnet-track-visitor-inf.aspx