Tag Archives: error proof dynamic named range

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