Excel Dynamic Named Range [4 Ways]

Excel Dynamic Named Range is one of the Excel features that not so many Excel users know about. This article will make Dynamic Named Ranges closer to you. In the further lines, hints and tricks related to Excel Dynamic Named Range will be shown.

Dynamic Named Range with Excel’s OFFSET Function

In order to make a dynamic range, the Offset function can be used.

Excel’s Offset Function

Here is the syntax of Excel’s OFFSET function:

OFFSET (reference, rows, cols, [height], [width])

What the arguments mean in the function:

1. Reference – Reference from which you want to base the offset.
2. Rows – Number of rows, up or down from the reference cell.
3. Cols – Number of columns, right or left from the reference cell.
4. Height, width – Define the height and width of the selection, from the referenced cell.

Dynamic Named Range with OFFSET Function

In the Name Manager dialog box, it can be used for defining Dynamic Named Ranges.

Here is how you can do that:

Go to the Formulas tab, in the Defined Names group of commands, click on the Define name command, and you should get the New Name dialog box like this:

A Dynamic Named Range will be created to select all a range of cells that starts one cell lower than A1, in the same column, and selects the number of columns and rows given in K1 and K2 cells, from the example of the image below:

The formula in the “Refers to” field looks like this:

=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)

All the elements in OFFSET function can be made dynamic. For example, I would like to select a number of dates for some specific day.

Here are some outputs:

=OFFSET (Sheet1!$A$1; 1; 0; 6; 2) = It will refer the range A2: B7

The formula that will depend on entries in K1, K2, K3, and K4 should look like this:

=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)

As any other function in Excel, OFFSET can be combined with other functions that provide the number as a result, too. Let’s say that I would like to select all the Tuesday dates except the one in the last week, by taking Monday as a reference. Based on the same example, the formula should look like this:

=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)

Read More: Excel Dynamic Named Range Based on Cell Value (5 Easy Ways)

Similar Readings

• Excel Dynamic Range Based On Cell Value
• Excel VBA: Dynamic Range Based on Cell Value (3 Methods)
• How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)

Dynamic Named Range with INDEX Function

Other functions can also be used for Dynamic Named Ranges, like INDEX, for example – you would like to select all the “Mondays”, no matter how many of them there are:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))

If you would like to make the reference dynamic also, you can use a combination of formulas. Let’s say that you want to enter a name of a day in one cell, and then to select all the dates in the table that belong to the day.

=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)

Read More: Excel OFFSET Dynamic Range Multiple Columns in Effective Way

Dynamic Named Range with VBA

In their everyday work, professionals face situations where they need to perform the same operation over and over. The same thing might happen when creating Dynamic Named Ranges. In order to avoid repetitive tasks, VBA can be used for this, also.

VBA works exactly in the same way as formulas, just you need to follow the syntax that is characteristic for adding Named Ranges.

The syntax goes like this:

ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”

For example, one of the previous examples can be easily applied in VBA. A complete macro would look like this:

Sub naming()

ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”

End sub

Read More: Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline)

Dynamic Named Range with Blank cells

You can often face issues when you don’t have all the cells populated, or, in other words, some of the cells are blank. The most important is to know the definitions of the formula used to find a reference. For example, COUNTA is a function that counts all NON-BLANK! cells in a range. It depends if you want to include blanks or not.

The image below shows the situation with a blank cell:

If you want to select all the “Mondays”, then using the formula like in previous examples would select every Monday but the last one, since there is a blank cell – A5. The solution for this would be using a different formula – the combination of the formula:

=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>””)*ROW(Sheet1!$A:$A)))-1;1)

It will select all the cells from A2 to A7.

The previous lines and examples should help you to perform your work faster and more efficiently. Dynamic Named Ranges represent a huge area with many options to explore and can be a useful tool, depending on your skills and creativity. It is shown that there are many combinations, and it is up to you to find their application.

Read More: Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)

Download Working File

Download the working file from the link below:

Related Articles

• How to Use Dynamic Range VBA in Excel (11 Ways)
• Data Validation Drop Down List with Excel Table Dynamic Range
• Create a Dynamic Chart Range in Excel (2 Methods)
• How to Create a Range of Numbers in Excel (3 Easy Methods)