Hi,
It's just a range name that you create with Insert Name Define. The only difference is that I made it a dynamic range name so that as you add new grades the name will expand automatically.
For instance I could simply have defined the name as range A1:D21 on the Data sheet. However if you add a new grade on row 22 the formula will not find it. Hence in these circumstances we use what are referred to as dynamic range names. In this case
which uses the Offset() and Counta() functions to define the range limits. So the first bit , OFFSET(Data!$A$1,0,0 says start the range in A1 and offset it by zero columns and zero rows. i.e. it still starts and is locked to A1 since we use the absolute $ character.
Then the second and third elements of the formula, the two COUNTA(() functions are used to define the height and width of the table. So COUNTA(Data!$A:$A) counts the number of non blank cells in column A, hence when you add a new grade in row 22 the counta function will record 22 rather than 21. Similarly the second COUNTA(Data!$1:$1)) function counts the number of non blank cells in row 1, i.e. 4. I could just have used the number 4 but I like to cover all bases in case another column is ever added to the table.
Regards
Bookmarks