Search Contextures Sites

Excel -- Pivot Tables -- Pivot Play PLUS Add-In

Thanks to Ron Coderre, who created this add-in. If your Pivot or Query Table is based on an External Data Query, Pivot Play PLUS was built to help you easily edit the Connection String to the data source and the Query itself. It also contains a handful of other features to help you manage your pivot and query tables in Excel 2002 and Excel 2003.

Pivot Play Add-In Features General Usage Processing Options Data Source Settings Global Settings  About the Developer  Download Pivot Play PLUS To download the add-in file click this link: PivotPlayPLUS.zip  (for Excel 2002 and Excel 2003 only) Install Pivot Play PLUS Installation Instructions

General Usage The Main Page (see illustration below) opens with a list of all Pivot and Query Tables that are on the active worksheet (along with key information about each of those tables). If a cell within one of those tables is selected when Pivot Play PLUS is engaged, that table will be the default item selected on the list. Whenever a listed table is selected, additional information about that pivot table displays in the lower section of the Main Page. If the table data is based on an External Data Source, the Connection and Query information will be displayed and the Edit Settings button will be visible. Otherwise, a message that the source is not based on external data displays. To change the Connection Info or Query Info, click the Edit Conn/Qry Settings button. to open the Edit Settings For page.

Processing Options When a table from the list is selected, one or more option buttons, depending on whether the selected item is a Pivot Table or a Query Table. Each button's text indicates its functionality: Refresh Data: Retrieves the latest data into the Pivot or Query Table. Rename: allows you to give the selected Table a more descriptive name. Clear Invalid Field Names: removes dropdown list items that have no representation in the source data. (Pivot Tables ONLY).

Data Source Settings Clicking on the Edit Settings button opens a window displaying the Connection and Query details. Use this page to: Edit the Connection Settings to the data source. The upper section of the page displays the current Connection settings. This is where you change the location of the data source and any other Connection String settings. Edit the Query Settings that extract data into the pivot cache. The lower section of the page displays the current query settings. Edit the SQL in this section.

An additional feature in this section is the Read Query From a Cell button, which allows you to select a single cell in the workbook that contains the SQL to be used by the Pivot or Query Table. That reference can be a cell reference or a named range. In either case, the contents of the cell are read and written into the Table settings; the Table itself is NOT linked to the cell Finally, the Connection Settings and Query Settings sections have buttons to restore their respective settings to the original values. NOTE: If the edited settings prove to be invalid when saving, you may continue editing the them or click the Restore button see the original values.

Global Settings The Turn GetPivotData On/Off button toggles Excel's automatic GetPivotData function generation on/off. The Reset Pivot Play PLUS Startup Settings button returns the Pivot Play StartUp options to their defaults. This includes displaying the Pivot Play splash screen upon opening.

About the Developer When he's not sailing, Ron Coderre is a Business Systems Analyst and Application Developer in Boston, where he works within the Finance organization to provide extensible, automated, process re-engineering solutions targeted at increasing the productivity of financial analysts and reducing analytical task duration. Inherent in his finished products are intuitive user interfaces, accurate data, the ability to easily adjust data scenarios, and publication-ready reports. Ron is also an Essbase expert and provides technical training.

Ron Coderre

To install the Add-in: Download the PivotPlayPLUS.zip file Unzip the file Save the PivotPlayPLUS.xla file in your Microsoft\Addins folder. For example, in Windows XP, this might be: C:\Documents and Settings\Contextures\Application  Data\Microsoft\AddIns (If you have previously installed the PivotPlay.xla add-in..Delete that file) Open Excel Choose Tools | Add-Ins Click the Browse button Find and select the PivotPlayPLUS.xla add-in Click OK, click OK A Pivot Play PLUS command will appear on the Data menu. No Help is available for the add-in. If you have comments or suggestions, please contact ddalgleish@contextures.com

 

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home

Excel Tips

Ron's Sample Spreadsheets

Contextures contact information Last updated: July 3, 2010 1:17 PM

Privacy Policy