Report lists from a table
- Nov. 14, 2021
 | Example files with this article: |
Introduction
Formulae, formulae, formulae, … We all need to use them in Excel. And in other software products, like TM1. While we can lookup the relevant formula in (online) help resources, it's not a bad idea to compile a list of our own. Maybe you are only interested in certain formulae instead of all formulae ? Maybe you want to give the report your own twist ? This is what I want to show here: making a maintainable, reusable, reporting.
You can argue that this is a one-off task. Formulae won't change a lot, will they ? No they won't. However, certain formulae get added, other formulae get discontinued. Therefore, the list is not fixed. Just look at Excel and the new functions we have seen over the last couple of years.
If you want a nice presentation layout, this can impose restrictions on the maintainability. In the end, we want to minimize the work as much as possible, and have Excel work for us. The idea is that a table can hold the functions and the information, while lookup functions retrieve them and show in a nice one-pager or similar.
As an example, we see the list of TM1 functions that can be used in Turbo Integrator. The functions are organized by category. Within each category, we have a list of function names sorted alphabetically. Each category has 2 columns. In the report layout, we also have 2 blocks, left and right. This is just an Excel file where cells are populated - not in the usual way by entering the names (or copy/paste) but rather, formulas are used to retrieve the names from a table on a different sheet.
The table is looking like this. All cells are hardcoded text (copy/paste where possible, then cleaned up for consistency).
It took me a while to come up with that list, the real list is much longer as you can imagine. Keeping the list up to date is a challenge too !
Reference and look up formula names
In the (hidden) columns G, H, J and K, I will store the numbers of the function as they appear in the table, column "Name_cheat". Line by line, each formula has the same horizontal "distance" in columns:
- Column A looks in column G
- Column B looks in column H
- Column D looks in column J
- Column E looks in column K
This is important: this ensures that we can safely copy/paste the same function to all other "cells" and the relative cell references will continue working. The formula itself is INDEX/MATCH, so not that fancy at all.
However, 1 big problem appears. What if new functions get added, others get removed ? It's not very dynamic. Even though we can increment the numbers in the hidden columns with very basic formulae (just the previous cell +1), it's still far from perfect.
In a follow up article to this one, we will remove these limitations by using dynamic array functions.
What is nice though, is the benefit we have from 2 rather small VBA procedures:
- A Selection Change event: click on a formula name and on the right hand side, details will appear
- A Double click event: double click on a formula name and you will be taken to the row of that formula in the table
In case you are interested, please have a look at the VBA code in the file. Besides these 2 VBA events, there are other useful procedures as well. Some might need some tweaks here and there, because this file is just an excerpt out of my main cheat sheets.
Stay tuned for more on this topic !
