Monthly Archives: August 2012

Creating a progress bar using MS Excel charts

While writing complex VBA programs, or programs that take a lot of time to run (e.g. calculating country/region-wise sales from third-party data), it is useful to give the end-user some sense of the task completed/remaining. I am essentially talking about the progress bar that we see in most software, websites and operating systems. Excel does not have a built-in progress bar that one can use for programming purposes. However, there are several ways of creating one. In this post I am going to use an Excel chart to create such a progress bar–please note that this is not the only way or the best way of creating a progress bar, but definitely one of the easy and effective ways. The end product will look like the one shown in the GIF image below:

ms excel chart based process bar

The first step is to create a 2-D bar chart using data for two series (lets say, Total and Current): Continue reading

Dynamic named ranges – using COUNTA may be error prone in some cases

We often create dynamic named ranges while making Pharma dashboards. These ranges can be used as an input list for data validation, combo-boxes or just as a reference for VLOOKUP formulas. During some of our tutorials and projects on this blog we have already explained how dynamic named ranges are created. These named ranges use two basic formulas – OFFSET and COUNTA.  An example of such a named range formula may be:

Test_Name_Range  = OFFSET(Sheet1!B1,0,0,COUNTA(Sheet1!B1:B5),1)

The above formula returns a single column named range referring to column B. The length of the list/range is defined by the COUNTA formula that calculates all the non-blank cells in a given range and returns the number of rows. This approach works well if the data present in the range is continuous, i.e. there are no blank cells in between—however, this may not be an ideal scenario. Say the user missed out a couple of cells, or maybe you used a macro to drop the data and some blank rows were also dropped. In such cases, this formula would not return accurate results. To be more precise it will return a range shorter in length (by the number of blank cells) as compared to the real range. This can lead to serious miscalculations, especially in case of dashboards and other automated tools—just imagine working with third-party sales data (such as IMS) and missing out on a couple of channels or territories/bricks; each row of data is so precious for the analysis.

wrong range dropped by offset and counta formula

One work around for this is to use the last used row number for finding the right range. This can be done my using the below mentioned formula: Continue reading