How do you use arrays in a GetPivotData Function?

ease20022002

Board Regular
Joined
Jun 4, 2005
Messages
233
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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???
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top