Analyze Multiple Worksheets in Excel
Excel’s PivotTable feature can handle this request, but you have to invoke it with an uncommon setting called Multiple Consolidation Ranges. This is only the second time I’ve needed that particular setting. First, let’s get all the required data into a single consolidation workbook. Rather than make copies, we’ll create links to the original data. Create a new workbook and insert a worksheet for each of the workbooks you want to consolidate. Click one worksheet tab, then Ctrl-Click the rest to select them all. Enter “Item” in cell A1 and “Source” in cell B1. Because the tabs are all selected, these entries will appear in every worksheet. Click a single tab to end the multiple selection.
Open the first of the source workbooks and highlight just the list of items. Copy it to the clipboard. Switch to the consolidation workbook and click in cell A2 of the corresponding worksheet. Read More»
