The Reporting Templates Tool is a mini search engine of a database of accounts and their report type in Excel. When an account is entered:
- all its profile details are displayed using the VLOOKUP function (looks up the database and returns a single value), and
- a list of what templates are required to generate that account’s report are enumerated (looks up the database and returns multiple values).
In this tool, we have a source tab (General) to keep all information about each account – could be what region that account is from, how is it delivered to clients, what reporting period does it follow, and all other specifications required of each account.
In another source tabs (Temps), we enumerate all the templates required to be downloaded and saved of each account before generating its report.
All tabs we utilize the Excel table feature so updating the list in the future won’t hurt our formula. If you got confused what I’m talking about tables, you might wanna go here first.
Then we have our Main tab, which serves as our dashboard of the “search engine.”
We input on cell B2 the account we would want to look up to, then the dashboard displays everything about that account selected.
The upper portion mainly uses the VLOOKUP function. Each item looks up the account from General tab and returns what is needed. That’s pretty easy, right?
The lower portion uses the INDEX-SMALL combo, which should be an array formula. The INDEX-SMALL combo sort of does the VLOOKUP function, but instead of returning a single value, it returns or enumerates multiple values on a range.
Download the sample file: [SAMPLE] Reporting Templates Tool.xlsx
Or! Wanna save yourself from all the work and build yours by just plugging in your data? Just message me below to get the unlocked version!