Aug 05, 2014

So, you have developed an exciting formula somewhere that works perfectly by extracting some value from a specified range and you are quite proud of it. Then one day, someone adds a new row to that range and your formula no longer works because the range was set to only include a set number of rows and columns. Today, I’ll show you how to keep these ranges in tact when using older versions of Microsoft Excel. Let’s take a look at this example. Here, we have named the highlighted table ‘Contacts’ and used this name in the VLOOKUP function to retrieve the phone number for Andrew Fuller.

Now let’s add more rows, and try to retrieve Jack Frost’s phone number using ‘Contacts’ name in the VLOOKUP. As you can see, it says that Jack Frost doesn’t exist because it is only searching within the ‘Contacts’ range. You should either manually modify the name to include the new rows or create a dynamic range to expand automatically. The latter option is what we want to follow.

To do so, we need to create two named ranges. The first named range called ‘temp’ encompasses all the cells within the sheet that is a superset of our table. To do this, go to your ‘Formulas’ tab and click on the ‘Name Manager’ button. In the dialog box, click on the ‘New’ button and type ‘Temp’ in the ‘Name’ field. In the ‘Refers to’ field,  enter the following:

=OFFSET($B$4,0,0,COUNTA($B:$B),3)

This formula gives a range that includes the entire range that we really want, meaning that ‘temp’ is a bigger range than what we are aiming. Note, you should change these values according to the position of your table. I’ve explained the different parts of the above formula in relation to my data.

• ($B$4,0 refers to the beginning of our range.
• COUNTA($B:$B) returns the number of non-blank rows in the first column. We could also write COUNTA($B4:$B1000000) instead but the former is more elegant.
• The number 3 at the end refers to the number of columns we are including in the range.
• So you should change these values according to the position of your table.

Now, we need to create the second named range called ‘Contact’ with the following formula:

= OFFSET(temp,0,0,MIN(IF(ISBLANK(temp),ROW(temp),””))-4,3)

In this case, the number 4 refers to the row number of the beginning of your range, which in my data, is the row number of ‘Joe Blogg.’ Again, number 3 refers to the number of columns we have included in the range. Now that you have your two ranges set up, you should be able to add any number of rows to the range and use the same formula without any modifications. The elegance of this method is that if you have any other table located underneath the ‘Contact‘ table, that table will not be included in the ‘Contact‘ table, as long as there is a blank row between the two tables as shown in the figure below.

In the above image, we can see that the search for ‘XCD2’ does not return any value because the ‘Contact’ range refers to $B$4:$D$10 and the addition of the rows in the other table does not expand the range. If you add new non-blank rows immediately after row 10, then the ‘Contact’ range will expand to include those rows. It should be mentioned that in Excel 2007 onwards, you will not need to create dynamic named ranges because you can turn any table into ‘Table Data’ by selecting the whole table and going to the ‘Insert’ tab and clicking on ‘Table.’. Now, as you add rows or columns to the table it automatically expands and therefore, any formula that is based on this table will be updated with the new range.