Let’s Excel
Published in

Let’s Excel

MS Excel — Dynamic Report Header, Part II

Tips — Going a Little Deeper from A Prior Post

Balance Comparative Reports Are Common — Need to Make Comparison Column Flexible!

The Task

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.

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

Print View of Comparative Balances Report

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.
Comparative Month Selection Field — Default is Prior Year Month-End Date
  • Using drop-down list, select prior month as option
Comparative Month Selection Field Populated — Report Column Heading Updated
  • Click “Clear” to empty Selection Cell and return to default
Comparative Month Selection Field Cleared — Returns to Prior Year Month-End Date

1 — Create the Data Validation List

  • Create a pivot table from the data table source — showing ONLY month end dates in descending order:
Data List for Data Validation Use
  • Create the drop-down box and connect the data-table source (“Data → Data Validation” on Excel Ribbon):
Setting Up Data Validation Parameters
  • Test drop-down list
Active Drop-Down List

2 — Create Formula to Update Automatically

Formula to provide Automatic Date Updating

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.”

Formula analysis

=IF(ISBLANK(E1)=TRUE,VLOOKUP(+B9,MonthEnd_Lookup,2,FALSE),+E1)

Formula Analysis

Wrap Up

It’s actually not as bad as it looks — and, wow, is it handy if you are responsible for putting together reports!

Good luck!

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.

About Don

“It’s time for DIFFERENT”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store