Can GETPIVOTDATA be used for this?

XLML

Active Member
Joined
Aug 15, 2003
Messages
407
Hi,

I would like to be able to use the getpivotdata function in Sheet 2 below based on 2 criteria, the "BusUnit" and "Account". Can this be done using some variation of GETPIVOTDATA? Example of Source Data (Sheet1) and Destination (Sheet 2) are below.

Thanks in advance,
XLML
PT2.xls
ABCDEFG
1BusUnitAccountAmountSum of Amount
210006022575BusUnitAccountTotal
320206043060100060225200
41000602251251000 Total200
52020604303020206043090
62020 Total90
7Grand Total290
Sheet1
PT2.xls
ABCD
1BusUnitAccountGETPIVOTDATA
2100060225
3202060430
Sheet2
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Hello, you'll either want to generate a P.T. report or use Suproduct().
Book1
ABCDEFG
1BusUnitAccountAmountSumofAmountAccount
210006022575BusUnit6022560430
3202060430601000200
4100060225125202090
520206043030
6
7
8
9
10BusUnitAccountGETPIVOTDATA
11100060225200
1220206043090
Sheet8
 

XLML

Active Member
Joined
Aug 15, 2003
Messages
407
Thanks Nate. I tried using sum product. I named the ranges on Sheet 1 - "BusUnit", "Account" and "Amount". The formula returned 0. Do you know why this is?

XLML
PT2.xls
ABCD
1BusUnitAccountAmount
210006022575
320206043060
4100060225125
520206043030
Sheet1
PT2.xls
ABCD
1BusUnitAccountSumProduct
21000602250
32020604300
Sheet2
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
You are definitely missing a ) at the end of the call.

The ranges absolutely must be of the same size. Did you get it to work with simple range references?
 

XLML

Active Member
Joined
Aug 15, 2003
Messages
407

ADVERTISEMENT

Hi Nate, This seems weird. I have used SumProduct successfully in the past. As you can see below, I deleted the names and used simple ranges and it doesn't work. Also, Excel is giving me an error message when I put closed parentheses at the end of the formula.

Any ideas?
XLMLSCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT>
PT2.xls
ABCD
1BusUnitAccountSumProduct
21000602250
32020604300
Sheet2
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
And while you're at it Nate, tell us where we can score one of those cool Toronto Maple Leaf caps you be sportin'.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

Hmmm, maybe the original data is text? Try copying a blank cell, select your original data, paste special -> add.

Tom, in Toronto. :) I'm sure there's some good online offerings out there. Thought it was odd, someone offered me $200 for my alternat. Leafs sweater the other day... Not sure that covers the cost though. :eek:
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Thanks for the info...I will pick one up on line. The $200 seems steep (maybe it was Canadian dollars?); game-worn jerseys always command more than off the rack. It's the best time of year now, down-hill to the playoffs after the AllStar break !!
 

XLML

Active Member
Joined
Aug 15, 2003
Messages
407
Thanks for all your help, Nate. I rekeyed the data on another spreadsheet and the Sumproduct formulal worked.

XLML
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
You are welcome. :)
XLML said:
Thanks for all your help, Nate. I rekeyed the data on another spreadsheet and the Sumproduct formulal worked.
Yikes! That sounds like a chunderfull process going forward... :whistle:

I might experiment with the Paste Special Method I mentioned, text to columns and/or the clean() worksheet function to coerce this data into a more workable set.

Tom, nope, USD, and I agree, the real deals are pricey to get into, e.g.,

http://www.icejerseys.com/toronto_maple_leafs.php

Mine also has a MLG commemorative (we're moving) patch on it. Not sure what mine cost originally, my girlfriend bought it online from a Canadian retailer. :) Hasn't seen too much ice time in terms of game-worn, although it has seen the odd pool hall, bowling alley, etc... :LOL:

Agreed, very excited for the remainder of the season! :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,490
Messages
5,764,671
Members
425,229
Latest member
Rashid mahmood

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
Top