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