Now let’s discuss the details. I have made the tool in-line with our earlier discussion (about the key fields in any company profiling request). So let’s start with the interface.
I have kept it really simple—all one needs to do is select a company from the drop-down box on the right-hand-side top corner and everything changes accordingly. There are four main sections to this dashboard 1) company overview 2) recent news 3) pipeline molecules details and 4) financial details
Overall the dashboard follows a three sheet structure. The display sheet is where the user makes the request (the drop-down), the processing sheet is where all of the relevant data is fetched by simply using formulas, and the database sheet is where I have placed all the company specific data. I have put the data for only three companies here, but you can increase it to any number you want—the tool is completely flexible on that front.
While the display and the database sheet would need inputs from the user, I would ideally hide the processing sheet, if I were to send it to someone—unwanted changes here would result in tool malfunction.
So, this is how the tool works:
Ok, let’s list down the different formulas, objects and tricks that I have used for this tool:
- Formulas : CHAR, OFFSET, IFERROR, MID, FIND, LEN, SUBSTITUTE,
- Objects: Combo Box (Drop down)
- Tricks: Named range,Dynamic chart
The basics for all these MS Excel tips and tricks can be found on the Dashboard-101 page. I will discuss the more specific ones in my next post. But if you want to explore some of the slightly complex formulas I have used in this tool, you may want to explore the section in the ‘Processing sheet’ that deals with ‘recent news’.