Some more information about the work behind the scenes that makes these Excel sheets easy to use but able to complete complex calculations. All identifying information has been removed and the sheets may have been simplified for display here.
Drug Dose Calculator
This sheet calculates a drug dose based on patient information. All the cells are locked and can’t be selected, except for the seven input cells. Each input cell has data validation on it to ensure input is sensible and if data is missing the user is prompted to complete the necessary cells. If the input values fall outside of a set range, then it defaults to “Contact the Pharmacy Department”. The rows and column indicators are hidden, so it doesn’t look like a spreadsheet. The final calculation uses interesting rounding. The value needed to be rounded to a multiple of 20 with 1 to 13 rounding down and 14 to 19 rounding up.
Automatic Report Creation
This is an example of a ‘database’ sheet, a report button, and a report. Similar to the information you would get with a pivot table, but using the subtotal feature of excel means the sections can be expanded and the details looked at. Creating the code to do this in VBA means the user doesn’t need to have any experience with excel and can just push the button to get the report. The button runs code in the background of the spreadsheet to take the data from the database, filters it, create subtotals for each gift type, formats the report and saves the report as a pdf file. I made this for a Christmas themed training session, so this was Santa’s toy database.
Automation of monthly report to employees
The control panel shown here displays the information for each employee and allows the user to make changes to email addresses and which employees get emails each month. The information on the amounts is automatically updated from the source each time the spreadsheet is opened and each month a report is generated to summarise the data for that month for each employee.
Dashboard
This is a KPI dashboard. It is dynamically coloured, so that as the values change so do the colours. For example, the background of the KPI 6 box will be green when the value is less than 3, orange if it is between 3 and 5, and red if it is greater than 5. Similarly, the dials on the right-hand side will change colour as their values change. The whole background will also change based on the overall score in the top right-hand corner.
Please contact me if you would like further information, examples, or trial versions.