According to Bullen, Green, Bovey, & Rosenberg (Excel 2002 VBA, p. 143) pivot tables can handle up to 256 variables -- which I beileve equates to columns (column headers). Are all 285 of your columns actually being included? In either case, it sounds like you are pushing Excel's pivot table limit.
EDIT: By the way, just in case you mean 285 rows instead of columns: if you are ading rows below your originally defined data field, then Excel probablyis not picking up the additions. You need to redefine the data field (use the pivot table wizard on the table and go "Back" to the field definition window -- or even better, create a Named Dynamic range for the data field and enter the Name as the data field).
See the following site map of OzGrid for a link to Dave's tutorial on Dynamic ranges.
Your attachment did not make it. There is a 1 MB limit to attachments, -- also, if you went through Post Preview attachements get dropped and need to be reattached before posting.
P.s. Did you see the EDIT to my first post? You were posting while I was editing.
Hi,
You didn't edit my post, I just meant that we were working at the same time (I didn't realize that you were back online) and I added to my first post while/after you had looked at it.
In looking at your file, I don't see any pivot tables that have more than 20 or so variables (which refer to more than 20 or so columns as their data field). Hence, I am suspicious that your initial post referred to the number of rows. The edit now in my first post addresses this. (However, the pivot tables in your posted example do not call more than 235 rows -- or else come from external so I cannot tell how many rows are called. Hence I'm a bit confused and am not sure if I'm addressing your question.)
Yes, you can. The problem is that your datafield in the Summary Sheet pivot tables is defined in absolute terms:
'Total Extract'!$A$5:$R$235
When you add data below row 235 the pivot table does not know it is supposed to include the extra rows.
Two possible fixes.
The best is to create a Named Dynamic Range for your data that adjusts to the new size of the data field. Replace the 'Total Extract'!$A$5:$R$235 with the Name of the Named Dynamic Range. The thread I mentioned in my EDIT to the first post will hep you do this. The attached is an unrelated example file that makes use of dynamic ranges in this way to refer to data fields.
The second, less elegant, but should work technique if to chnage the 'Total Extract'!$A$5:$R$235 to something like 'Total Extract'!$A$5:$R$1000 (or whatever maximum number of rows you might eventually include). This will introduce blanks into your Summary tables, but these can be supressed by double clicking on the "Band" headers and selecting "Blank" or whatever shows as that in the "Hide items:" dropdown.
i can see exactly what you are saying about about changing the 'Total Extract!$a$5:$r$235 to a named dynamic range; but because the data comes from an external data source i can't seem to create a named dynamic range. this is really killing me! is there a way around that you can see from the example sheet i posted?
EDIT: Forgot that your file is too large to post. Put the following in the "Refers to" box for your dynamic range (INSERT > NAME > DEFINE) and then change the data source for each of the three pivot tables on the Summary sheet to the Name you give to this dynamic range in the "Names in Workbook" box.
=OFFSET('Total Extract'!$A$5,0,0,COUNTA('Total Extract'!$A:$A)-4,COUNTA('Total Extract'!$5:$5))
Pre-edit text: The attached has a named dynamic range that defines the data on the Total Extract sheet. (The name is ExtractData) The three pivot tables on the Summary sheet now use this name as their data field.
The dynamic range assumes that the imported data on the Total Extract sheet will always have the same format: that is, it will have the headers in row 5 and have a total line at the bottom that should be ignored.
If you study the offset function and Dave's tutorial a bit, I think you'll see how this is done.
Ok, I deleted the extra sheet and deleted a few columns from the Total Extract sheet to make the file small enough to post. (The deleted columns are not screwing up the pivot table yet, because you aren't trying to use the headers that no longer exist.)
See if this helps a bit more.
Don’t have an account yet? Register yourself now and be a part of our community!
