MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table


Posted by Susie on May 21, 2001 7:44 AM

My workbook has 3 sheets; 1 is the database, 2 is the Pivot Table, 3 is a spreadsheet that requires getting certain info. from the Pivot Table. Example, I'm looking for Total Number of Females and this number is in the Pivot Table in G100. On the 3rd sheet in the appropriate column, I type: =G100 and it returns the number, let's say 100. The problem is if the database changes which changes the pivot table and changes the location of the number. Example, I add a new employee in the database and now the total number is on G101. Now the 3rd sheet where I have the formula = G100 is not correct now.


Posted by lenze on May 21, 2001 9:24 AM

Susie:

Use the GETPIVOTDATA() Function.

lenze

Posted by Susie on May 21, 2001 9:52 AM

I'm kind of new at this. Can you be more specific, as in step-by-step. Where do I put this formula, etc.

Posted by lenze on May 21, 2001 10:02 AM

Place the function where you want the data returned. Basicly, where you had =G100 in your example. Activate the function wizard by clicking on the = sign next to the formula bar or choosing Insert<Function. Find and choose GetPivotData . You can now click the help Icon for detailed instructions. Hope this helps.

lenze

Posted by Susie on May 21, 2001 11:12 AM


Need more help please. Presume Y4 in the Pivot Table has column "Total Count of Job Group" and Row 77 is the row for the total of columns relating to "Middle Management" and row A177 contains the entry "Middle Management Total" and Y177 contains the answer which is 28. How would I enter this?

Posted by lenze on May 21, 2001 12:09 PM

Susie: The format for GETPIVOTDATA is =GETPIVOTDATA(Pivot Table, Name). In the cell you want to display the result, enter =GETPIVOTDATA(Pivot Table Reference,"Middle Management") where Pivot Table Reference is either the name of your Pivot Table, A named range which includes your Pivot Table, or easiest, a Cell Reference in you table) See Help for the GetPIVOTDATA function for examples. If you wish, email me a sample file and I will show you how it works.

lenze