In a previous post, we created a simple pivot table from a list of Excel data. Today, we’ll link the pivot table data to a spreadsheet using the GETPIVOTDATA function.
Note: You can follow along with the how-to by clicking on the following Excel file link: The GETPIVOTDATA Function.xls
(In the attached file, the tab labeled “Sheet1″ contains the pivot table, and tab “Sheet2″ contains the spreadsheet)
We’ll start with this pivot table as our source data:
We’ll want to link the labor expense data in the pivot table to our spreadsheet, which compares the planned amount versus the actual expense:
We’ll need to have both the pivot table and the spreadsheet file open. If the pivot table and the spreadsheet are in the same file, so much the better. In the spreadsheet file, we’ll go to cell C6, which will represent the actual labor expense for the month of April for project CB735. You could type in the formula as it appears, but we’ll take a shortcut. Type the “+” sign, and go to the sheet containing the pivot table, in this case Sheet 1. Select the appropriate cell, in this case N5, which is the Labor Expense for April for project CB735. Hit Enter. The formula bar for cell C6 should appear as follows:
If we were to copy this formula to the other cells, we would wind up with the same amount in all of the cells, which is not what we want. Instead, we’ll edit the formula to facilitate easy copying. In the formula, replace the constant “CB735″ with the variable “$A5″ (absolute column and relative row), and the constant “April” with the variable “C$3″ (relative column and absolute row):
Finally, we’ll copy cell C6 to the other cells. You can now see how the addition of absolute and relative cell references allows the spreadsheet to pull the correct data from the pivot table:
Print This Post
|
|
|
|
|
 |
Related Posts -
A Simple Mortgage Amortization Table Kevin over at the No Debt Plan blog recently advised his readers to avoid mortgage accelerator programs like the plague. He reasoned that you could set up your own mortgage accelerator program by adding 1/12 of a payment as additional principal to each month's payment. I agree with Kevin. You...... -
Low Budget Circumstance has forced my hand, To be a cut price person in a low budget land - from Low Budget, by The Kinks, circa 1979 They say the more things change, the more they stay the same. I remember when that song came out; we were embroiled in controversy with...... -
A Backup of a Backup Photo by Mocodragon Backup my hard drive? How do I put it in reverse? - unknown One of my coworkers had a near-meltdown last week when her computer wouldn't recognize her thumbdrive. The thumbdrive contained all of her important files, and was her only copy. Now, I could have......
Related Websites -
Simple Retirement Calculators and Spreadsheets Spreadsheets can be useful as a simple form of retirement calculator. I have found a few that are free to use. If you are comfortable working with spreadsheets and are interested in using them as retirement planning tools, download them and try them out. (If you are reading this in...... -
How to Clean Up Outlook Contact Phone Numbers using VBA Introduction [/caption] I got a new phone and when synchronised with Microsoft Outlook, the phone numbers don't dial out properly because of spaces & STD bracketing. It's an LG - the previous Nokia was great, as was it's synchronising software! Solution I used VBA in a workaround via Microsoft Excel! ...... -
Free Financial Management Software When it comes to your getting out of debt, having the right software can actually make a real world of difference. Some people tend to manage their efforts at debt reduction with something that is as simple and as straight forward as a computer spreadsheet while other people tend toward......
