It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one sheet for displaying summary for each month. In this way, a total of 13 worksheets will be displayed on your workbook. Indeed, this was a question to me…
Can it be done?
Of course it can be done. BUT we could do it doesn’t mean we should do it.
My suggestion is to hold only ONE sheet to display the monthly summary on which users have the flexibility to select any month (or any period) by using Pivot Table Timeline.
Here’s the layout of the proposed solution:
You may download a sample file to follow along.
Note: The file is saved as .xlsm (macro-enable) format. Please enable content when prompted if you wish to see the auto-refresh action.Continue reading