My Pivot Tables can't handle it!!!!
Posted by Ben Abramovitch on October 17, 2000 2:56 PM
Basically my problem is, that i have an excel 2000 database that is immensely large (50k+ lines) and whenever i refresh a pivot table generated by this, it has to take ALL the data, and
a)takes forever
b)crashes 50% of the time its so big
Ive been trying to get it so i just grab the headings of my rows (month, day, year, name....etc) and then
skip to the relevant data, at lets say line 30000
by holding down ctrl and grabbing diffrent ranges..
when i do so, i get something like this in the range box:
Sheet1!$A$1:$B$1,Sheet1!$A$4:$B$9
It put the comma between the 2 sections on its, own so it looks like it can handle it, and furthermore it doesn't allow you to do unlimited of these, after 5 or 6 it stops you from adding another. When i try to go NEXT, though it says its not valid
If i could just delete some of the data i would, but there are some pivot tables which require ALL the data
and thats why theres this problem now...:( Any suggestions or know how to fix this problem would be
GREATLY appreciated!!!!
Sorry for the long message, but couldn't think of how to write it any smaller ;)
Ben Abramovitch
--Its just to big!
Posted by Dave Lazarus on October 19, 2000 7:05 PM
Two ideas:
1-Create an OLAP data cube, but I don't know how to do.
2-If you have lots of redundant data (like several fields uniquely defining a single row), consider creating two or three smaller tables in Access and linking them through MSQuery when you get external data for your pivot. I don't know if it's faster, but easy to do. Write me if you want some more help or VB to do it.
