We needed a SharePoint Dashboard that the end user could select filter criteria once and it would be pushed to all pivot tables on multiple sheets within one workbook

You have now connected a SharePoint filter to a single workbook that contains multiple worksheets with multiple pivot tables and are driving all of the pivot table filters with one single filter from the SharePoint Dashboard.

Pivot Tables are a powerful capability of Excel, however MOSS now provides the capability to create a pivot table from data in a SharePoint list. This example will show you how to use a SharePoint list to create a pivot table and then display it back in SharePoint using the Excel Web Access web part.