When you change a criteria value in a cell, Excel can use formulas and conditional formatting to expand or contract your report automatically, without macros. Here’s how.
 |
by Charley Kyd, MBA Microsoft Excel MVP |
Excel Tables are a powerful feature introduced in Excel 2007. Not only can you report from them directly, you can use them as a source of data for dynamic reports, including variable-length accordion reports.
The first task is quick and easy; the second is a lot more interesting. Let’s look at both.
Report Directly from Excel Tables
Suppose you have a table like this and you want to generate a call-assignments report for each sales person.
The easiest approach is to report directly from the Table. For example, If you wanted to report on Alyson, you could click on the Sales Person filter and choose Alyson from the list, giving you this result:
You could print her report, and then do the same for Peter.
Read Full Article: Create Variable-Length, Dynamic Reports Linked to Excel Tables
Source: ExcelUser