Excel 2002 from '97 Pivot table reference woes 3

[beasleyd]

Hey everyone.

Having a slight problem referencing a Pivot table in Excel 2002.

I do a performance report that tracks the sales of the agents of my company.

I pull raw data from Access and paste it into Excel. I then take all that data and create a pivot table.

Next I go to our pre-made performance report sheet, and reference cells with those in the pivot table, then click-drag the lower right corner to autofill for all the agent.


In Excel '97, when I wanted to reference it was easy. In the 'Report' sheet, I'd click on, say, B6, type '=', then navigate to the 'Pivot' sheet, and click on the corresponding field, say, F7.

Therefore, it would look like this:

B6 = 'Pivot'!F7

Then I could easily drag and autofill... making it go all the way to B30 = 'Pivot'!F31

---HERE'S THE PROBLEM---

In 2002, when I click on "F7" it doesn't say "='Pivot'!F7"....it now says:

=GETPIVOTDATA("Annual_Commission",'Pivot'!$A$3,"Plan_Type","ANNUITY","Name","Smith, John")

This will not autofill on the performance report. Please tell me how I can get it to display like it did back on '97!!

Thanks,

David

 

[Zig1]

Hi beaslyed,

I had and solved this problem today.

First, the purpose of the GETPIVODATA is to get a result, and the same result regardless of the amount of lines in a Pivot Table. This can be useful if you are looking for a total, but the total is on different lines based on differents sets of data supplied to the Pivot Table.

This feature can be toggled on and off. To do this, you must insert the GETPIVOTDATA icon on the Pivot Table Toolbar. To do this, insure that the Pivot Table Toolbar is present. If it is not, View Toolbars, then check Pivot Table. Drag the toolbar up to the top of your page and anchor it with the other tool bars so it will always be there. Next, click on the triangle on the right side of the toolbar, select Add or Remove Buttons and click on the GetPivotData Icon. Click on any cell to get out and the new icon should be on the right side of the pivot table tool bar. By default it is turned on. Click on it to turn it off. With it off, you can reference cells, like Excel 97, with it on, the GETPIVOTDATA will be used. Turn it on and off as you need to.

For more information, go to Microsoft Knowledge Base article 287736 XL2002: GETPIVOTDATA Formula Is Automatically Created When You Try to Create Simple Link. The URL is:

http://support.microsoft.com/defaul...port/kb/articles/q287/7/36.asp&NoWebContent=1

Hope this helps,

Zig1

 

[beasleyd]

Oh. My. God.

A month later... I'd forgotten I even made this post.

Thank you so much for finding that out. This time of year the number of reports based on pivot data is skyrocketing, so I needed a fix for this... it was annoying manually changing formulas or using Find & Replace.

You get a star for this one Thank you thank you thank you!!!

 

[KenWright]

That was a new one on me - Thanks Zig - Have another star.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------