Microsoft Excel — How To Create A Date-Variable Dynamic Report Header

No Need to Change Your Header — Make it Dynamic!

Presentation Excerpt — Sept 21, 2016 IMA Heartland Regional Conference

Overview

In September 2016, a presentation I gave at an accounting conference touched on the preparation of dynamic reports — reporting where data automatically updates based on a date driven variable.

I recently received a follow up question to that presentation:

“How can I create a DYNAMIC report header, containing the date selected in the drop down box?”

This post is focused on solely that aspect of what many accounting & finance professional deal with everyday — managing and updating your financial report descriptions!

Report Headers — From Static To Dynamic

The Objective

This is our starting point.

SALES REPORT — STATIC HEADER

And the end game…

SALES REPORT — DYNAMIC HEADER

The Date field that drives the report, and the dynamic header, is based on a data validation drop-down box. The user chooses the report date, as follows:

DATE SELECTION DROP-DOWN BOX

Create Report Header Input Table

Start by setting up an input table, which enables us to identify what we will put in the Header Description.

• I put the table on the SAME SHEET, outside of the report print range.

Input Table — Drives Dynamic Header for Report

The components of the Input Table — for this report — are:

• Month (Text)
• Year
• Period — YTD, LTM, Month?
• Report Title — Comparative or Trend?
• Text String — this is the text that populates the Report Header
• Purpose — just a reference note

Populate Input Table

Various Excel functions are used to accomplish this dynamic header. Whenever I refer to an Excel FUNCTION, I link to an overview and description (PLEASE explore if you don’t understand!).

The process for updating the Input Table is as follows:

Month — It is necessary to:

• identify the month (DATE function),
• show it as Text description (September, rather than “9”) — TEXT function, and
• display it in uppercase format, to conform with existing format — UPPER function

CREATING THE MONTH TEXT DESCRIPTION — “SEPTEMBER” FROM THE DATE SELECTED FIELD

Year — use the YEAR function

YEAR FUNCTION TO SHOW YEAR FROM A SPECIFIC DATE

Period and Title — these are input based on report type (they should never change once the report is set up — i.e., they only get input ONCE).

INPUT PERIOD & TITLE — ONLY ONE TIME

Text String — we have finished the creation of the Report Header variables, so now it is the simple step to create the string of text (using the CONCATENATE function).

• NOTE — I don’t actually use the CONCATENATE function. An Excel shortcut of that is to connect strings of text using the “&” — see link above (HOWEVER, you are concatenating the text)

CONCATENATE THE CELLS — DESCRIPTION DONE!

Let’s Test It Out

September 2015

SELECTED SEP-15 FROM DROP-DOWN

June 2016

CLICK DROP DOWN → SELECT JUN-16 → DONE!

Work through this one time and you will nail it!

Good luck!

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