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.
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