|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 25th, 2002, 09:05 AM
|
#1 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Does anyone know if the GETPIVOTDATA function is likely to get unreliable with a big pivot table? It seems to work all the time with small pivot tables, but I have this pivot table based on 8000*40 data cells, with 30 fields ad 5 pivoting fields and I am always having problems, returns #N/A even when the values are in the pivot table. It is a worry.
Should I perhaps not be using pivot tables for such data, and perhaps think about using DSUM or something ( I am not familiar with database functions at all by the way ). Many thanks, RET79 |
|
|
|
May 26th, 2002, 05:17 AM
|
#2 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Millbank, London, UK
Posts: 1,790
|
RET,
the info you require via GETPIVOTDATA that is giving you the N/A error, have you tried getting the same info via DSUM or SUMPRODUCT yet to see if you also get the same error message ? if you can't get an answer using ither methods, then it's likely it's the data that's at fault (or the arguements you're using) rather than the size of your pivot table just try it on a couple of entries and see what happens
__________________
:: Pharma Z - Family drugstore :: |
|
|
|
|
May 26th, 2002, 07:05 AM
|
#3 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Chris,
Thanks for your reply. It has just occured to me that maybe I don't need to use pivot tables at all! A 3 way sumif could well do the trick, for instance take a look at the pivot table I am looking at now:
To see the formula in the cells just click on the cells hyperlink The above image was automatically generated by[HtmlMaker V1.25] If you want this code, click here and Colo will email the file to you This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo as all I need to do is sum up column NUM for each condition. RET79 [ This Message was edited by: RET79 on 2002-05-26 06:14 ] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
May 26th, 2002, 07:17 AM
|
#4 |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,319
|
A 3 way sumif could well do the trick...
RET Just curious: How would this "3 way sumif" look like? Aladin |
|
|
|
|
May 26th, 2002, 07:21 AM
|
#5 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Aladin,
I don't really know! But the idea being that you sum the relevant rows in the NUM column when VAL-TYPE = 5, VAL-CLASS = A and REC-TYPE = FPP. I am not much of an expert on this I must admit, but because I am having so many problems with the GETPIVOTDATA function I must look into a more reliable alternative. All I am essentially doing is summing up different data based on certain conditions. Any help appreciated, RET79 [ This Message was edited by: RET79 on 2002-05-26 06:21 ] |
|
|
|
|
May 26th, 2002, 07:31 AM
|
#6 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Just to explain better about where I am using this GETPIVOTDATA thing, it is used in tables such as this one, based on the pivottable posted above:
To see the formula in the cells just click on the cells hyperlink The above image was automatically generated by[HtmlMaker V1.25] If you want this code, click here and Colo will email the file to you This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo I think I need to get an alternative to this approach as the GETPIVOTDATA function is very unreliable, so am looking for perhaps a lookup solution. RET79 |
|
|
|
|
May 26th, 2002, 07:39 AM
|
#7 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,319
|
Quote:
You have to use either a SUMPRODUCT (or an equivalent array-formula) or DSUM formula. =SUMPRODUCT((VAL-CLASS="A")*(REC-TYPE="FPP"),(NUM)) where VAL-CLASS is the name of the range that houses the val-class values, REC-TYPE the name of the range that houses the rec-type values, NUM the name of the range that houses the num values that you want to conditionally sum and "A" plus "FPP" are conditions that must hold for the relevant ranges. If you don't have too many pairs of conditions, you'll be OK qua performance. DSUM is not hassle-free: you have to set up for every pair of conditions a criteria range. Aladin |
|
|
|
|
|
May 26th, 2002, 07:54 AM
|
#8 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
OK Aladin, I have managed to do this problem now with array formula and GETPIVOTDATA. Now I am really interested in your sumproduct method, but if I understood you correctly, does this mean that you have to set up named ranges?
In some cases, I will require looking up data across 5 columns. Also, the underlying data may change so I would prefer to have the formulas in these table to be rigid and still work if we change to more accurate data etc. Thanks, RET79 |
|
|
|
|
May 26th, 2002, 08:20 AM
|
#9 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,319
|
Quote:
The ranges do not have to be named. However, you can envisage using names or range specifiers for ranges that frequently. [ This Message was edited by: Aladin Akyurek on 2002-05-26 07:21 ] |
|
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
