Extracting Data from Pivot Tables
Posted by Daniel Brickell on September 28, 2001 2:33 AM
I am trying (very unsuccessfully) to use the GETPIVOTDATA function within Excel. I have a pivot table which is linked to an outside source via an ODBC link. On the second worksheet of the file i am trying to pick up the values contained within the pivot table. I have tryed using a number of different variations to the formula but with no success. A variety of different error messages have appeared including #REF, #N/A and one message that said i had a circular reference within the formula. If anyone out there can help i would be eternally grateful.
Yours frustratedly
Dan
Posted by Mark W. on September 28, 2001 5:27 AM
Can you post one of your GETPIVOTDATA attempts?
I can then ask you questions about its arguments
and help you uncover the source of your problems.
Posted by Daniel Brickell on September 28, 2001 5:38 AM
Mark
I have two rows called account No. (5 digit number) and account names. I have dates split into months across the top.
Example of formula are:
=GETPIVOTDATA(Sheet1!A4:N285,"50110 Jul-01")
=GETPIVOTDATA(Sheet1!A4:N285,Equal Share 7/1/2001)
Equal Share is one of the account names and 7/1/2001 was just trying the date in a different format. Interestingly the bottom formula comes up as acircular reference which seems very odd.
Any suggestions you may have would be great.
Cheers
Dan
Posted by Mark W. on September 28, 2001 5:51 AM
Use...
Posted by Dan B on September 28, 2001 6:16 AM
Re: Use...
Tryed both of these and they come back with showing #REF!.
Posted by Mark W. on September 28, 2001 6:54 AM
Okay, then...
I'm gonna conclude that you have applied a "mmm-yy"
format to what would otherwise be dates such as
7/5/01, 7/24/01, etc. If I've guessed correctly
you have two alternatives...
1. Add a month column to your data list that
contains the formula, =TEXT('Date',"mmm-yy"),
which will convert your 'Dates' into a textual
Month value. Then change your formulas to...
=GETPIVOTDATA(Sheet1!A4,"50110 Jul-01")
=GETPIVOTDATA(Sheet1!A4,"Equal Share Jul-01")
2. Group your dates into months and years using
the Data | Group and Outline | Group... menu
command, and then change your formulas to...
=GETPIVOTDATA(Sheet1!A4,"50110 2001 Jul")
=GETPIVOTDATA(Sheet1!A4,"Equal Share 2001 Jul") Tryed both of these and they come back with showing #REF!. : =GETPIVOTDATA(Sheet1!A4,"50110 7/1/01")
