MS Excel — Dynamic Report Header, Part II
Tips — Going a Little Deeper from A Prior Post
In a prior Medium post, I explained how to create report header that is dynamic and updates automatically based on the Dates and Data Included.
Microsoft Excel — How To Create A Date-Variable Dynamic Report Header
No Need to Change Your Header — Make it Dynamic!
In this post, I go deeper…
I’m working on a client reporting project and received the following question:
“In our balance comparative report, we compare to the prior year-end balance and the prior year. Can this be changed to compare to the PRIOR MONTH balance instead of the PRIOR YEAR-END?”
Obviously, we can. However, how about if we make it so the report would compare to the prior year end (by default), and the user could easily modify the report to change this to whatever month they want?
So we did. And now we are going to walk through how it was done!
Here We Go — Step By Step!
The following Excel concepts come into use here:
Here is a final view — print view of the document
Here is User view:
- By default, the comparative month is the prior year end month (in this case, December 2017). If the “comparative cell” is empty, the column date will always be the prior year month end.
- Using drop-down list, select prior month as option
- Click “Clear” to empty Selection Cell and return to default
1 — Create the Data Validation List
- Create a pivot table from the data table source — showing ONLY month end dates in descending order:
- Create the drop-down box and connect the data-table source (“Data → Data Validation” on Excel Ribbon):
- Test drop-down list
2 — Create Formula to Update Automatically
In plain English, this formula tells Excel:
“IF the comparative cell [E1] is blank, use VLOOKUP and get the prior year month end date, IF NOT, use the comparative cell date value.”
It’s actually not as bad as it looks — and, wow, is it handy if you are responsible for putting together reports!
NOTE: The report data is populated by referring to the date headings in the column and retrieving the relevant month data from a data table (pivot table) using the HLOOKUP function.