GetPivotData with variable criteria input?

[Guest]

I am working on a sheet that will look up various data from a massive pivot table so that the user can change one cell, and from their input the data will update automatically.

The pivot table contains summarized data from a 90MB .DBF file of detailed financial information. The problem is that the number of criteria cells to be used will vary. On the right hand side of the pivot table I have 5 cells (5 levels of detail) that can be used as criteria, but the user may want to get the data from various levels of detail. This is where I'm having a bit of trouble.

Is there a way to have the GETPIVOTDATA function draw it's criteria (FIELD / ITEM pairs) from a cell reference, similar to the way the database function do (i.e.: DGET, DSUM, etc) ? Any other ideas?

Here's a sample of the criteria I want the user to be able to modify

RC1 RC2 RC3 RC4 RC5 Group
5 0 0 0 0 Salaries

This criteria would pick up the lowest level detail, but I want the user to be able to change the level of detail, for example by inputting:

RC1 RC2 RC3 RC4 RC5 Group
5 0 0 Salaries

With this criteria, I need to pick up all RC's starting with '500', which would include RC 50000, and 50001, and 50030, etc.

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

I tried using DSUM, but because Pivot Tables don't repeat the higher-level (further-right) labels, this function won't work for me.

 

[Debra Dalgleish]

There may be better ways, but if the criteria are filled in from the
left, you could use something like:

=IF(L2<>"",GETPIVOTDATA("Salary",$A$3,H1,H2,I1,I2,J1,J2,K1,K2,L1,L2),
IF(K2<>"",GETPIVOTDATA("Salary",$A$3,H1,H2,I1,I2,J1,J2,K1,K2),
IF(J2<>"",GETPIVOTDATA("Salary",$A$3,H1,H2,I1,I2,J1,J2),
IF(I2<>"",GETPIVOTDATA("Salary",$A$3,H1,H2,I1,I2),
IF(H2<>"",GETPIVOTDATA("Salary",$A$3,H1,H2),
GETPIVOTDATA("Salary",$A$3))))))

 

[Guest]

This did work! Thanks for the help and the quick reply!

I had tried a similar formula using if (len(a2=5),getpivotdata....,if(len(a2=4),get... (my RC1 through RC5 fields are populated with the sequential characters of the A2 field), but for some reason I can't figure out this didn't work. I even tried changing the order (len=1 first, and len=5 first) - neither worked for some reason.

I think I'll send a suggestion to Microsoft that the getpivotdata function be improved to pick up criteria like DGET and DSUM do, from table cells, and automatically ignore blanks, either that or improve the database functions to work with PivotTables.

 

[Guest]

This did work! Thanks for the help and the quick reply!

I had tried a similar formula using if (len(a2=5),getpivotdata....,if(len(a2=4),get... (my RC1 through RC5 fields are populated with the sequential characters of the A2 field), but for some reason I can't figure out this didn't work. I even tried changing the order (len=1 first, and len=5 first) - neither worked for some reason.

I think I'll send a suggestion to Microsoft that the getpivotdata function be improved to pick up criteria like DGET and DSUM do, from table cells, and automatically ignore blanks, either that or improve the database functions to work with PivotTables.

 

[Debra Dalgleish]

You're welcome! You can send suggestions to:

(e-mail address removed)

Put "Excel" in the subject line, so your suggestion will be forwarded to
the right people.