Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 49 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.

In the workbook that will contain your PivotTable, insert a new
worksheet and call it Data. 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.

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.
Copy this cell across as many columns as there are headings in your
data source. Then select Insert Name Define,
and in the Names in Workbook: field, type
PivotData. In the Refers to: box, type the
following:

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Click Add, then click OK. Next, to insert some code that will run
each time the workbook is opened, right-click the Excel icon (located
at the top left corner of the screen) 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

This shortcut isn't available 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.

To return to Excel, close the script
window or press Alt/-Q.

The preceding code includes only 1,000 rows of data. This figure
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, add a few hundred more to accommodate any growth in the
original table.

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

Save the workbook, close it, and then
reopen it, making certain that you enabled 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 row 1 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 you can hide this sheet
if you want by selecting Format Sheet Hide or
by using the method described in [Hack #5].

Now, to base a PivotTable on this dynamic
named range, select anywhere within the PivotTable, then 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 now the 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.