Several years ago, a reader asked me to help him with an Excel-reporting challenge that might sound familiar.
His challenge, he said, was that he wanted to create monthly reports for 27 divisions. He asked whether he could set up one report that applies to any division for any time period, and then choose the division and month from a list.
What he DIDN’T WANT, of course, was to create 27 different reports from scratch each month. That would send him to Excel Hell in record time.
What he NEEDED — but didn’t know it — was an interactive Excel report. This is a report that adapts automatically when we change the settings for dates, divisions, products, departments, regions, and so on.
Generally speaking, he wanted his data plumbing to look like this data plumbing diagram, where his Excel Formulas would reference certain Control Cells, which would specify the dates, divisions, and so on that he wanted each report to display.
That is, he wanted his reports to update as though they were linked to a PivotTable or to another interactive source of external data, but without necessarily using those tools.
We Excel users face this need all the time in a business setting. In fact, if you use Excel to report or analyze business data you probably need several interactive Excel reports right now.
Here are some articles that you might find useful…
…In How to Select Values from a Validation List in Excel, you can learn how to set up a validation list.
…In How to Select Dates and Regions in a Interactive Excel Report Linked to an Excel Database you can learn how to use two criteria to report from an Excel database using a simple helper column.
…In Interactive Dashboard Magic with Excel Slicers you can learn how to use slicers to update values in control cells.
