getpivotdata in excel 2007

=+GETPIVOTDATA(pivot1,$A$3 & " " &B$2 & " " &A5)

I'm trying to return pivot table data to another sheet.  The range of the 
pivot table is called "pivot1" and the reference cells represent (name of 
doc), (date), (procedure type).  I get a REF#
I have spent hours already and need a solution fast.

Please
0
Fern1 (5)
6/24/2009 6:10:02 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
1060 Views

Similar Articles

[PageSpeed] 54

Fern wrote:
> =+GETPIVOTDATA(pivot1,$A$3 & " " &B$2 & " " &A5)
> 
> I'm trying to return pivot table data to another sheet.  The range of the 
> pivot table is called "pivot1" and the reference cells represent (name of 
> doc), (date), (procedure type).  I get a REF#
> I have spent hours already and need a solution fast.
> 
> Please

Hi Fern,

Spend a few seconds looking at the syntax for GETPIVOTDATA and you will 
probably realize what you are doing wrong.

IME, constructing a GETPIVOTDATA formula is easiest like this. This is a 
bit verbose, but it goes quickly and is much easier than writing 
GETPIVOTDATA by hand:

1) Cursor in cell where formula will reside.
2) Type = and navigate to a data cell in the PT. Don't worry too much 
about the cell you select -- the data element (count of procedures, sum 
of billed amount, etc.) should be the one you want, but the category 
combinations do not have to be correct at this point. Press Enter. You 
now have a formula that returns the correct kind of data, but from 
specific (and perhaps incorrect) categories. Now generalize this to suit 
your needs.
3) Click the formula in the formula bar. A pop-up should reveal the 
syntax. As you hover over the parameters in the pop-up, they will 
underline as hyperlinks.
4) One by one, click the parameters in the pop-up. Note how the 
corresponding elements in your formula are highlighted.
5) Now you can carefully edit your formula, replacing the hard-coded 
categories with desired cell references.
0
smartin108 (170)
6/24/2009 10:48:50 PM
What formula do you get if you type an equal sign, then click on a data 
cell in the pivot table?
Adapt that by replacing the item names with cell references. For example:

  =GETPIVOTDATA("Qty",pivot1,"Doc",$A$3,"Date",$B$2,"ProcedureType",$A5)

Fern wrote:
> =+GETPIVOTDATA(pivot1,$A$3 & " " &B$2 & " " &A5)
> 
> I'm trying to return pivot table data to another sheet.  The range of the 
> pivot table is called "pivot1" and the reference cells represent (name of 
> doc), (date), (procedure type).  I get a REF#
> I have spent hours already and need a solution fast.
> 
> Please


-- 
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

0
dsd1 (5911)
6/24/2009 11:33:09 PM
Reply:

Similar Artilces:

Including Hyperlinks in Document Exported from Excel to PDF
I have about 150 charts in an excel workbook that are updated weekly an exported to a PDF document. I want to include a master index at th front, and sub-section title page indexes throughout the document, wit hyperlinks to the named charts and sub-section title pages. However, find that chart sheets can't be hyperlinked, and if I locate the chart as objects in worksheets then create hyperlinks to those sheets, th hyperlinks do not carry over into the PDF. I have tried using both PD writer and PDF distiller, and am using Adobe Acrobat 4.05. Is there a way of either ensuring that the hyp...

Excel
When I copy a sheet to the end of a workbook, it changes all of the data on the existing sheets. Any idea if this bug has been worked out in recent updates or versions of Excel? On 5/27/04 9:15 AM, in article 444257df.0405270515.3c3bccf7@posting.google.com, "Andrew" <adewing@princeton.edu> wrote: > When I copy a sheet to the end of a workbook, it changes all of the > data on the existing sheets. Any idea if this bug has been worked out > in recent updates or versions of Excel? Can you explain in more detail what is happening. This is not a bug or problem that I am ...

Word to excel, excel to word
Hi, Heard that excel can merge into Word, any idea how to proceed with the merging. Any replies will be greatly appreciated. Thanks Like mailmerging to produce labels? If yes, you may want to read some tips for mailmerge. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. ron wrote: > > Hi, > > Heard that excel can merge into Word, any idea how to proceed with the > merging. Any replies will be greatly appreciated. > > Thanks -- Dave Peterso...

how do I create more than 266 columns in an excel sheet?
My excel sheets have by default up to column IV (aprox. 266 columns). I need to insert more than this and also have problems with the option of "grouping" columns. When I have most of 266 columns in use and several "groupings", when I try to minimise them all (one by one), after a few, appears a warning pop up messge with a message "objects cannot be moved outside the sheet" and the action cannot be done (cannot mimise that group of columns and not anymore until I expand/ungroup others). The number of columns is limited to 256 columns. This cannot be incr...

AutoExpand In Excel
I was wondering if anyone had managed to use the auto expand function in excel in the way that it is used in access. I would like to do this in a combo box to filter aprox 1000 records that I have in the box. ...

Unbound option groups and form's RecordSource in Access 2007
I have a form that contains unbound option groups and an unbound combo box in the form header section. The form itself is bound to a query, but in the Load event I set the form's RecordSource to something like: SELECT * FROM [query] WHERE FALSE; This has the effect of hiding all of the data fields on the form, leaving only the option groups and combo box showing. The option groups are used to limit the items that show in the combo box, and the combo box is used to select an item to view on the form. When a selection is made in the combo box, the form's RecordSource is modified ...

excel found unreadable content 03-04-10
I created a worksheet, with very simple formulas, saved in Excel 97-2003, had re-opened and made changes to the file several times, then today I try and open the file and it stated that Excel found unreadable content and asked if I wanted to recover content. So I click yes, and then another error message stated that excel was able to recover formulas but some data in the cells may have been lost. So my entire document is blank. The data are very important and would really like some help figuring out how to recover the file! Please. Thanks Some people have said that using OpenO...

getpivotdata
Hi, I have a pivot table with multiple columns and need to create a summary report by different views using getpivotdata. Below is just a small summary. The actual data runs into hundreds of rows. The Pivot looks as follows Col 1 Col 2 Col 3 Col4 Country Segment Pricing Various months....................... A AA XA XB XC Total AA AB XA XB XC Tot...

pictures and text in excel
Hello After inserting a picture in Excel, how can I get it to be in the background. In other words, for the text in the spreadsheet to appear over the picture. Thanks You don't. The picture layer is always "over" the spreadsheet layer. "NABRIL" <NABRIL@discussions.microsoft.com> wrote in message news:9F198B9D-4867-4B98-A424-F4A5DAFDE2F4@microsoft.com... > Hello > After inserting a picture in Excel, how can I get it to be in the > background. In other words, for the text in the spreadsheet to appear over > the picture. > Thanks Format>Sheet...

Configuring outlook 2007 #2
I like to keep most of my E mails in the inbox for about 12 months and then archive and keep indefinitely. This works well for most of my E mail accounts. The facility to search old E mails easily is particularly useful in relation to my activity as a Councillor. However I have joined our local recycling group and am finding myself overwhelmed by the volume of E mails arising from this subscription. I only use the subscription occasionally but on those occasions it is useful to shift surplus items and I would therefore prefer not to unsubscribe. I have eased my problems by setting up a ...

About Excel VBA
Hello: Is there some important differences in the Visual Basic of Excel 2003 and Excel 2007? If I practice VBA in Excel 2003, could I have problems to do the same applications in Excel 2007? -- Miguel M. Yal�n http://mmyv.com Answered in public.excel.programming "Miguel" <responderalgrupo@invalid.invalid> wrote in message Hello: Is there some important differences in the Visual Basic of Excel 2003 and Excel 2007? If I practice VBA in Excel 2003, could I have problems to do the same applications in Excel 2007? -- Miguel M. Yal�n http://mmyv.com ...

Creating lables in Excel
if I have a database created in Excel with Names, address, ect. can you create lables for mail outs like in Word? Excel alone is poorly suited for creating labels. Use Word's mailmerge with Excel or Access as the data source. Assuming you are using Word for the mailmerge and Excel for the source see these sites for help. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm Gord Dibben MS Excel MVP On Tue, 30 Sep 2008 11:35:01 -0700, Jessgolden143 <Jessgold...

loosing columns in excel 2000
I'm a long time Excel user & database my clients info there. During the last week I've lost columns. The first time about eleven columns vanished, & now another one has disappeared. I cannot attribute this to any particular action. The first time I was hiding the columns in question to work on other information. The hidden columns were gone. This most recent was while I was rebuilding the missing information, saved for the day, shut down over night & restarted the following day, a column was missing. I'm using Office 2000 Professional on a Pentium 1.4 GHz, Window...

have dates entered from a list of data into an excel template
I want to create a calendar (I downloaded the 2005 template). I have a list of dates with an adjacent list of "to dos". How can I automatically have the dates inserted into the appropriate calendar day? Chip Pearson has just such a calendar. No need to re-invent the wheel. http://www.cpearson.com/excel/download.htm -- Don Guillett SalesAid Software donaldb@281.com "Diane" <Diane@discussions.microsoft.com> wrote in message news:32E56745-4E4B-419F-9692-9680E7E5C3DA@microsoft.com... > I want to create a calendar (I downloaded the 2005 template). I have a list >...

Microsoft excel and ms access
I have 40 forms that I need to fill in and generate reports. I don't want to use access for this purpose, neither I want to use PHP programming. I am wondering whether or not it is possible to use excel as an entry form and access database as a data recording unit. Plz. Suggest Regards Sam. Heres a couple of links that may help: Better Solutions - http://www.bettersolutions.com/excel/EHX116/LT423111411.htm Bristol Uni - http://www.bristol.ac.uk/is/learning/documentation/excelxp-t5/excelxp-t5.doc Also try doing a Goole search "sAMBEDAn kOIRALa" wrote: > I have 40 for...

Closing Excel
Is there a way to disable pressing the "x" at the top right corner of the screen from closing an Excel document. Here's my dilema: I have created workbooks that I allow beginner-level users to use to access data. In these books, there are buttons that perform all of the actions, including saving and closing the documents. However, I have trouble making the users press the buttons instead of pressing close or"x" in Excel, closing the document wherever they want and not leaving it appropriately for the next user. Is there a way to temporarily disable them from...

Can't import Outlook Express into Outlook 2007
Here's the situation. user going from Outlook Express in XP [32-bit] SP3 to Outlook 2007 SP2 in Windows 7 64-bit. The old Outlook Express files [.dbx, etc.] are in a folder. When I try to import the messages into Outlook 2007, I specify import mail, choosing OE/WLM but it detects the never used WLM account [I thought it didn't come with win7 but it did pick up the dummy "Welcome to WLM" message]. It at no time asks to choose a folder or anything. Data was taken from a backup. Original computer was stolen. I know of a long way but is there anything faster? ...

Excel weirdness!!!!
I have a text file containing values that I import into excel. Once the values are in an excel sheet I do a sum of them all. (currency - 2dp) The final sum is displaying as 441992.489999999990000 - why would this be? None of the individual values show a greater degree of precision that 2dp!!! In the text file there are no values with more than 2dp! Why is excel calculating incorrectly. I know I can do rounding etc. but what I want to know is why excel is playing up! CIA,Matt. -- MattShoreson ------------------------------------------------------------------------ MattShoreson's Pr...

Return Excel IF statement to a different worksheet?
How do i return to results of an Excel IF statement to a different worksheet Hi You can only return results into the cell in which the formula is. In order to do what you want, you'll need to put the IF formula into the cell in which you want to return the result. Andy. "addme99" <addme99@discussions.microsoft.com> wrote in message news:8B30E9F6-22EA-4C49-9075-9EF7FD370122@microsoft.com... > How do i return to results of an Excel IF statement to a different > worksheet ...

Excel message box
I am looking for help with a macro because the macro I have so far is not close to accomplishing my desired procedure. Can you help me? Sub MsgBoxmacro() MsgBox "Question #1 here" MsgBox "Question #2 here" MsgBox "Question #3 here" MsgBox "Question #4 here" MsgBox "Question #5 here" MsgBox "Question #6 here" MsgBox "Question #7 here" End Sub I have seven questions in range A1:A7. I would like to create a macro that when activated will perform the following functions. Display 1st question (cell A1) in a...

Excel 2003 crashes loading excel files created Excel 2000
We Have Anumber of excel files created in Macintosh Excel 2000 with graphs. These sucessfully open using Excel 2000 but all crash when using Excel 2003. The PC is on a network with XP profesional operating system. ...

in excel how do enter today's date on a laptop #2
on a laptop that has no seperate numeric keyboard how how do I key control+ to get todays date ? hi, try ctrl ; regards FSt1 "bency" wrote: > on a laptop that has no seperate numeric keyboard how how do I key control+ > to get todays date ? ...

Office XP Excel 2002
Two questions: 1) Where do I put a custom template so it is available under templates in Excel? I looked thru the knowledgebase and they gave me folders that don't exist in my XP. 2) How do I get rid of the stupid task pane on the right that keeps popping up? I can remove it, but when I close and re-open Excel, it shows up again. I want it to function like it did in Excel 2000. 1. Pretend to save a file as template and look in what folder it saves default to. Then put it there, mine opens to a folder called templates 2. I have excel 2003 and it can be removed under Tools>opt...

Filters in Excel
Hi all, need help with filters in Excel XP can anyone help. Website with tutorials, screenshots etc. Thanks, Sketch. Sketch Debra Dalgleish has info with screenshots on her site. http://www.contextures.on.ca/tiptech.html Gord Dibben Excel MVP On Sat, 3 Jul 2004 00:03:57 +0100, "Sketcher" <sketcher@eircom.net> wrote: >Hi all, > > >need help with filters in Excel XP can anyone help. Website with tutorials, >screenshots etc. > >Thanks, > >Sketch. > ...

Word to Excel #2
I cant seem to find a forum for MS Word so I am posting this query here. I have a Word document with 926 pages in it. It has been created from a mail merge so each individual page is a new letter with an address at the top of it. Unfortunately we have lost the orginal source file. Does any one know of a way to strip the first 6 or 7 lines out of a Word doucment and put them into Excel? Thanks David Price Technical Support ABI Building Data Ltd www.abibuildingdata.com There is a forum for Word, it's under this section (Office) and Word (at the very bottom). >-----Original Mes...