Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help getting data from large table (get pivot data?)
Hi,
I have a table that is about columns by up to 65k rows. I have a pivot table based on this giant table. I am looking for a quick way to get summary data if I'm searching up to 15 specific columns out of a possible 30.... The 30 are the total possible columns I may want to search but I would only need to search a max of 15 columns for any given operation. The problem is that those 15 could be different each time. I currently use macros to change autofilters have a Subtotal(109,...) formula and get that value but this is very slow. I was wondering if there is a way to extract this data using something like Get Pivot Data.... Again the difficulty is that I may need to filter on up to 15 columns at a time but those 15 columns might be different each time. My understanding of Pivot Tables is that you have to pre-build a pivot and then look for data in the pre-exisiting table. This wouldn't work for me because I can't have a pivot with 30 column fields open. If this doesn't make sense, please let me know. Thanks for any help, MikeZz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help getting data from large table (get pivot data?)
Mike,
You can construct a formula like this, where you want to sum of NumData from the pivot table starting in cell A3 sheet SheetPT, where Field1 is "Entry 1" and Field2 is "a value" =GETPIVOTDATA("NumData",SheetPT!$A$3,"Field1","Ent ry 1","Field2","a value") You can see the syntax by selecting a blank cell, typing =, and selecting a cell from the privot table. You can also use cell references like so: =GETPIVOTDATA("Value3",Sheet4!$A$3,A1,B1,A2,B2) where cells A1:B2 contain Field1 Entry 1 Field2 a value HTH, Bernie MS Excel MVP "MikeZz" wrote in message ... Hi, I have a table that is about columns by up to 65k rows. I have a pivot table based on this giant table. I am looking for a quick way to get summary data if I'm searching up to 15 specific columns out of a possible 30.... The 30 are the total possible columns I may want to search but I would only need to search a max of 15 columns for any given operation. The problem is that those 15 could be different each time. I currently use macros to change autofilters have a Subtotal(109,...) formula and get that value but this is very slow. I was wondering if there is a way to extract this data using something like Get Pivot Data.... Again the difficulty is that I may need to filter on up to 15 columns at a time but those 15 columns might be different each time. My understanding of Pivot Tables is that you have to pre-build a pivot and then look for data in the pre-exisiting table. This wouldn't work for me because I can't have a pivot with 30 column fields open. If this doesn't make sense, please let me know. Thanks for any help, MikeZz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I list every data combination from a large pivot table? | Excel Discussion (Misc queries) | |||
Macro to create pivot table from large data file | Excel Programming | |||
large data file problems - pivot table with vba | Excel Programming | |||
large data file problems - pivot table with vba | Excel Programming | |||
calculations in large data set and in pivot table | Excel Programming |