MS-Excel / General Formatting

---

Efficiently Pivot Another Workbook's Data

Use data residing in another workbook as the source for your PivotTable.

When creating a PivotTable in Excel, you have lots of options for your data source. By far the easiest and most powerful approach is to use data that resides within the same workbook. Unfortunately, for whatever reason, this is not always possible or feasible. Perhaps the data that resides in another workbook is entered daily, for instance, and the users entering the data should not see the PivotTable.

Using a dynamic named range will greatly decrease the refresh time needed for your PivotTable to update. As you cannot reference a dynamic named range from another workbook, this also means you prevented the PivotTable from referencing perhaps thousands of blank rows and causing the file size to increase substantially. This way, you can pull in data from another workbook, and then base your PivotTable on the data in the same workbook rather than referencing it externally. Let's walk through the steps.

1. In the workbook that will contain your PivotTable, insert a new worksheet and call it Data.
2. Open the workbook containing the data to be referenced, and ensure that the worksheet containing the data is the active sheet. In any spare cell on this worksheet, enter this formula:
   =IF(A1="","",A1)
   where A1 is the very first heading of your data table.
3. Select cell A1. Then cut it, activate your original workbook, and paste cell A1 in cell A1 on the Data sheet. This will give you the reference to the other workbook.
4. Copy this cell across as many columns as there are headings in your data source.
5. Select Formulas → Defined Names → Define Name on (pre-2007, Insert → Name → Define).
6. Type PivotData in the Names: field and type the following in the "Refers to:" box:
   =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
7. Click OK.
8. Next, to insert some code that will run each time the workbook is opened, right-click on the sheet tab and select View Code.
9. Then, double-click This Workbook (pre-2007, go to the Excel icon located at the top left corner of the screen next to the File menu option, right-click, and select View Code) and enter the following code:
   

       Private Sub Workbook_Open( )
        With Worksheets("Data")
        .Range("2:1000").Clear
        .Range("1:1").AutoFill .Range("1:1000")
        .Range("2:1000") = .Range("2:1000").Value
        End With
       End Sub
   

   Right-clicking on the Excel icon isn't available in Excel 2007 or on a Mac. On a Mac, you'll have to open the VBE by pressing Option-F11 or by selecting Tools → Macro → Visual Basic Editor. Then, Ctrl-click This Workbook in the Projects window.
10. Finally, to return to Excel, close the script window or press Alt-Q, then save your workbook.

The code in Step 9 includes only 1,000 rows of data. The number you specify in the .Range statement should always be greater than the number of rows you believe you will need. In other words, if your table in the other workbook contains 500 rows, specify a few hundred more than that to accommodate any growth in the original table.

Avoid using an extremely high rownumber (like 10,000, unless you actually have that much data), as this will greatly impact how quickly the code runs and the data updates.

At this point you are ready to check your macro. Save the workbook, close it, and then reopen it, making certain that you enable macros. The code you added will fire automatically and will copy the formulas in row 1 on the Data sheet, then automatically convert all but row1 into values only. This will leave you with a copy of your original data source, which will update each time you open the workbook.

Now, to base a PivotTable on this dynamic named range, select anywhere within the PivotTable, select Pivot Table from the Insert tab, and type =PivotData under Select a Table or Range.

In pre-2007 versions, select the Wizard option from the PivotTable toolbar. Click the Back button until you reach Step 1 of the Wizard. Select the first option, Microsoft Excel List or Database, click Next, and in Step 2, type =PivotData (the name of the dynamic named range). Then click Finish.

You will not experience the lag that often occurs when a PivotTable is referencing an external data source because nowthe data itself is stored within the same workbook. As an added bonus, because you can use a dynamic named range, the PivotTable is dynamic without having to reference heaps of blank rows, and the file is kept to a manageable size.