How do you use arrays in a GetPivotData Function?

[ease20022002]

Hi,

I am wondering how you would use arrays in a GetPivotData Function. What I am trying to do with out manually typing a formula in easily over a thousand cells, I want to set up an array and I suppose the arrays are entered into the Item arguments of the function.

Can anyone point me in the right direction to solve this problem? I have searched the web extensively and can not find anything on plugging arrays into the GetPivotFunction.

Thanks in advance for any help...FYI, I did this with using basic array design of Sum(IF, but my boss wants it linked directly to the pivottable, which makes sense, I just can't find anything on plugging in the arrays...

 

[Greg Truby]

I'm not sure I am understanding your question properly. The GetPivotData function is used to pull out a particular bit from a pivot table. I'm not quite getting how you are planning to pass in an array. If you have "thousands of cells" that are candidates for a GetPivotTable formula, I've got to wonder how ruddy big is the PivotTable!

Typically a gpt formula looks like so =GETPIVOTDATA("Qty",$A$3,"PN","A1606034B") Perhaps you could explain a little bit better what you're trying to do.

 

[ease20022002]

Hi, Thanks for the response...

Basically I inherited a project where we are supposed to modify existing applications and reports. Specifially, we design Loss Triangles for our insurance company. So basically, we summarize a boat load of claim data using a pivot table, but the pivot table isn't visually good enough for the actuaries to printout, etc., so what was done was another report was made with formula links to the pivottable. I got rid of that and used simple sum(if arrays...but my boss would like (if possible) to have the getpivotdata function used so if the pivot table is ever changed the report will still be accurately referencing the static fields in the pivot table...

To simplify:

A Pivot Table that is linked to a database with boku records. The pivot table incorporates several fields and is quite large as pivot tables go.

Once that data is generated, there are hardcoded formula links on the report worksheet linking to the pivot table, where the actuaries actually do their analysis...

So you're telling me it is impossible to plug in arrays into a getpivotdata function???

 

[Greg Truby]

I'm not sayin' whether 'tis or whether 'tain't. I'm wonderin' whether that's the best way of skinnin' the cat. I'd be more inclined to copy the PT and uncheck or hide some columns or something along those lines since using the GPD() function sort of assumes you know all of the summary points. If a new class/group/heading appears, will a report using GPD() formulae update to include it? Only if fed by one of Aladin's humdinger "list unique values" array formulae, I'm thinkin'. So I'm thinkin' based on the size of the PT your describing, that would create a lot of overhead. Which is why I'd lean to VBA & hiding/deselecting columns/rows.

Clear as mud?