A few months ago I wrote a review of Jordan Goldmeier’s Advanced Excel Essentials. I had a project recently, which I thought this approach would be ideal for. This post is to tell you how I got on.
Since reading Advanced Excel Essentials I have also taken Jordan’s Excel Dashboard Pro video course. This course follows similar principles, so I feel that I am familiar with Jordan’s approach.
The project was to create a tool for handling the calculation of both short and long term incentive plans for senior management. The tool had to track which cost centers, divisions and companies were used for the calculation of each employee’s incentive plan. There are approximately 60-70 different schemes each year, which also change from year to year. The shortest scheme is annual whilst the longest is a 5 year period.
Applying Advanced Excel Essentials
Initially, I struggled with the Worksheet and Table naming conventions recommended in the book. I knew about sheet code names, but I rarely used them. I had always been too lazy. Once I got used to this approach it became logical.
In apply the approach I stumbled across one significant issue. I use a lot of workbooks which are best used in manual calculation mode. The method shown in the book relies upon automatic calculation being active, otherwise some very strange things start happening. To make sure the model I built worked correctly with automatic and manual calculation, I introduced calculation points within the code to ensure it still functioned correctly when in either calculation mode.
In recent years I have started to use the ActiveX form controls. The book focused on using the standard Form Controls, and now, I am thinking that the extra control/formatting options with ActiveX are not worth the hassle.
The suggested approach of creating a worksheet based “Wizard” and using Index numbers to update the results in the back-end data table worked really well. It does create is quite a time consuming approach for data entry, so if the user is dealing with thousands of line items they may not be too impressed. But I’m sure it can be adapted to enter multiple lines of data at the same time.
I love this book. The structure and approach provides a great framework for anybody undertaking an Excel tool which is to be used by others. It tries to leverage the benefits which inherent in a spreadsheet, rather than creating a complex VBA application. It took me a while to get my head around it, just because it’s a different way of working. I am glad that I didn’t just read this book, but I actually applied it to this project. I now have a greater appreciation as I have applied the principles to a real world situation.
I highly recommend this book, which you can purchase from Amazon here.
The link to purchase the book are an affiliate links which means, if you purchase through these links I will earn a small commission.