Named Ranges can be used in formulas or a Drop Down list, to save typing the range location in each formula.
But what if more entries are needed to a Named Range? Usually, this would involve amending the named range for each change. There is no ready solution to automate this in Excel, but it can be done using some smart functions!
How to Create the Range
Let’s take an example and step by step build a dynamic Named Range in Excel:
First, create a Drop Down menu from the list below:
Do this first by giving this range a name and use this Named Range in the Data validation window.
First,name the range:
Select the range: A2:A6 and in the Name box (top left of the sheet) write Countries (this will be the name of the range)
If you go to Formulas in the toolbar and click on Name Manager, in the Name Manager window you will see the Named range you just created:
To Use this Named Range in the Drop Down menu, we will first select the cell/cells where we want our Drop Down list to appear and in the toolbar click on Data and Data Validation:
In the Data Validation window choose List in the Allow section and in the Source field enter the name of our range:
Click Ok and test the range in the drop down list:
Now if we add more countries to our list, we will not be able to see them in the drop down as our Named Range is static:
To overcome this issue, the range needs to be made dynamic, so that no matter how many countries we add it will keep on expanding.
This can be done by entering a formula in the Named Range window instead of the range itself.
Go to Formulas, Name Manager and click on the Named Range. In the Refers to field enter the following formula:
The OFFSET function can be used without the optional arguments: height and width, the to return the value of a cell.
If we use this optional arguments then the formula will return a range and used as a standalone formula in a cell it will resolve as an error.
But used in the Named range window it will return a range, which can be used to create a range that updates depending on how many items are in the list of countries.
In the first argument of this formula: reference, enter the first cell of our range: A2. This tells Excel where the range begins.
For rows and column variables, enter 0, as this original cell A2 will remain fixed in place as the start of the list.
height – is the height of our range. Entering 6 here for example, the range would be 6 cells high, so the range would be A2:A7 ( 6 cells) .
To make this height dynamic instead of a static number for this argument use another formula which is COUNTA, which counts the number of non-blank cells in a range.
The COUNTA formula:
=COUNTA(Sheet2!$A:$A), counts how many non-blank cells are in this range.
In the initial list of countries this formula would return 6 (we have 6 filled cells in this range):
In the second instance when we added 3 more countries, this formula would return 9:
This height variable has made the height of our range (offset formula) dynamic.
In the last argument of the offset function, either leave it empty (as it is optional) or just enter 1 (the width of the range is 1 column wide)
So the formula will look like this:
Save the Named Range with the new formula and test your Drop Down, you will see that the new countries we added are displayed:
Try and add some more countries to the list and they will be immediately available in the Drop Down: