# How do you use arrays in a GetPivotData Function?

#### ease20022002

##### Board Regular
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...

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.

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???

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?

Replies
3
Views
203
Replies
11
Views
607
Replies
5
Views
329
Replies
23
Views
930
Replies
5
Views
231

1,196,309
Messages
6,014,586
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back