Dynamic named ranges in Excel

Commonly in your input form you might have validation on a drop-down field ensuring the user picks their selection from a pre-defined list.

 

In this example we have named the range ‘ListRegions’ and set it across the cell range B4:B7 to ensure it includes all 4 regions.

 

dynamicnamedrange1

 

We’ve set the field up with Data Validation to lookup the list.

 

dynamicnamedrange2

 

So the drop-down list populates with the 4 available options.

 

dynamicnamedrange3

 

In a lot of cases, this static named range is adequate.

 

But what if you want it to grow when new entries are added to the region list? What if the organisation expands abroad and there are new regions to add. With a static list you’ll have to manually keep updating what cells the ListRegions range looks up against. This is where a dynamic named range is useful.

 

A dynamic range will automatically grow when entries are added while also maintaining the validation on the drop-down field. In this example the range begins in cell B4 on Sheet2 so the formula you should use in your Name Manager for ListRegions is:

 

=OFFSET(Sheet2!$B$4,0,0,COUNTA(Sheet2!$B:$B),1)

 

dynamicnamedrange4

 

And the drop-down field will automatically include the new entries.

 

dynamicnamedrange5

Leave a Reply

Your e-mail address will not be published. Required fields are marked *