Put your foot down with Microsoft Excel dynamic named ranges. Add some Power and Punch
Free Microsoft Office Tutorials
In this tutorial we will be dealing with excel dynamic named ranges.
What are Excel dynamic named ranges?
Watch this short tutorial
Dynamic named ranges allow you to have data that expands or contracts and the range reference will change to include the changes. While there are many ways to do this I am going to use the offset function and the count function to accomplish this. This is the most common approach. I use dynamic named ranges as a matter of course. When you understand how they are constructed using them becomes much easier.
You cannot find dynamic named ranges in the name box on the sheet. A dynamic named range is a formula that is created and edited in the define name dialog box. To locate this box go to the Insert tab on the main menu then Name /Define.
To create the dynamic named range. Add a name to the “Names in workbook:” then in the “Refers to:” box add the formula for the name.
I will explain the breakdown of the process involved.
Dynamic named ranges are essentially built on the offset function. As you can see from the illustration below the offset function has five arguments.
Let’s discuss Excel dynamic named ranges arguments one at a time.
- The first is a cell reference that is the starting cell for the range
- The second argument is the number of rows you wish to offset. By offset we mean just simply move to. For example if we wanted to start our range at F16 then the row number would be 1 if we wish to start a range at F14 then the road number would be -1.
- The third argument is the same as the second except that it refers to columns, if we wish to move one column to the right we would have the number 1 if we wish to move to the left we would add the number -1.
- The fourth and fifth are optional arguments that you probably don’t use regularly when using the offset function but it is these two optional arguments that make add dynamic named range possible because we will be replacing them with a formula that counts the number in a range. In the offset function if we wanted to refer to 15 rows then we would add the number 15.
- The fifth argument is the same as the fourth except that it refers to columns.
What will make this offset formula dynamic is for us to put it into the Define Name dialog box and create a named range with the offset formula. For our fourth argument where we see the row reference we will replace it with a COUNT or COUNTA function.
If we are referring to text then we would use the COUNTA function.
When we are referring to numbers we would use the COUNT function.
Have a look at the illustration below and you will see that we have replaced the row height with a COUNTA function. Simply put, instead of giving an absolute reference here we are allowing the formula to count how many rows have data in that range and then adding that resulting value to our formula.
Pretty cool, don’t you think.
This illustration shows the process.Note the 4th argument is replaced with the Counta function.
You can expand the range by increasing the number of columns referred too. This refers to 5 columns.
Double Excel dynamic named ranges
This illustration shows how to add Counta functions for both the height argument and the width argument. So it is possible to have the range dynamic in both directions by using 2 count formulas for the height and width arguments. The count formula for the width would reference the rows.
Application for this may be a bit limited, but it is good to understand how a double Excel dynamic named range is created.
Let me know your comments about this tutorial.