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:

• 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......
• Excel How-to: The SUMIF Function The SUMIF function is an easy way to get a subtotal of specific data that meets a certain condition, without going through the trouble of producing a Pivot Table.  It's most useful when working with long arrays of diverse data: Note: You can follow along with this how-to by opening......
• 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......

• Using the Name box A faster way to create a name is to use the Name box (to the left of the Formula bar). Select the cell or range to name, click the Name box, and type the name. Press Enter to create the name. (You must press Enter to actually record the name;......
• Semantic Integration of Multiple Excel Spreadsheets igoldsmid asked: The bsemanticb web technology with an advanced queries and reports across multiple spreadsheets and other disparate data integration engine running. The frustrating issues involved in integrating multiple spreadsheets and powerful graphical user interface for easily creating advanced data sources this is achieved. The bsemanticb discovery system sds bsemanticb......
• 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......