Advanced Excel Dynamic Named Ranges
Advanced Excel Dynamic Named Ranges - Expand/Contract Named Ranges
Advanced Excel Dynamic Named Ranges
I don't think it's any secret that I'm a pretty big fan of dynamic named ranges
.They are ideal to be used in a vast array of different situations. I thought I would show you some Dynamic Named Ranges with a twist.
BASED OFF LONGEST COLUMN OF DATA
A Dynamic Named Range formula for a table of data that occupies say A1:D may look like below:
Name: MyTable
Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A),4)
Where: COUNTA($A:$A) is being used to determine how many rows to expand down and 4 is the number of columns to include Starting from $A$1.
This of course will work fine if Column A is the column with the most amount of data and there are no blank cells. But what if you have no idea which of the columns (A:D) will have the most data and if there will be blanks?
Let's first address the blank cells issue. On the page, this link points to , you will see that numbers 3 and 4 address the issue of the possibility that there might be blank cells between data. That is;
3**Expand Down to The Last Numeric Entry**
In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)
If you expect a number larger than 1E+306 (a one with 306 zeros) then change this to a larger number.
4**Expand Down to The Last Text Entry**
In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)
As you can see though, this does not account for the possibility of text and numbers in the same column. However, we should always have column headings in a table and these are normally text. So to find out the last used cell in Column A we could use;=MAX(MATCH(1E+306,$A:$A,1),MATCH("*",$A:$A,-1))
This formula requires that there is BOTH text and numbers in Column A. So, while the Column heading will ensure we have text, we need to ensure there is at least 1 number. To do this, insert a new row at Row 1(select Row 1 and go to Insert>Rows) and then in A1:D1 enter the number 0. You can now hide this row (select Row 1 and go to Format>Row>Hide). To use this to determine which of the Columns (A:D) has the highest row number we would go to Insert>Name>Define and use these names with the formula below as the result for their "Refers to".ColA=MAX(MATCH(1E+306,$A:$A,1),MATCH("*",$A:$A,-1))ColB=MAX(MATCH(1E+306,$B:$B,1),MATCH("*",$B:$B,-1))ColC=MAX(MATCH(1E+306,$C:$C,1),MATCH("*",$C:$C,-1))ColD=MAX(MATCH(1E+306,$D:$D,1),MATCH("*",$D:$D,-1))
Now we can create one more Name (MaxCol) and have this one return the maximum number of the Names above. This would simply be;MaxCol=MAX(ColA,ColB,ColC,ColD)
Now we have this done we can replace our original Dynamic Named Range (MyRange) "Refers to" range with
=OFFSET($A$1,0,0,MaxCol,4)
This will ensure that are named range always expands down to the last used cell in Columns A:D and it doesn't matter if there are blank cells and/or a mix of text and numeric entries.
DYNAMIC RANGE WITHIN A RANGE
This Dynamic Named Range is ideal for a long text list that is sorted A:Z. We can also make use of the "List" feature of validation to make it even better. For the purpose of this example I will assume to list is in Column Aand has been sorted A:Z
Select B1 and go to Data>Validation choose "List" then in the "Source" type; A,B,C,D....Z
Now go to Insert>Name>Define and use the Name: AlphaList and for the "Refers to" use;=OFFSET(INDIRECT(ADDRESS(MATCH($B$1 & "*",$A:$A,0)+1,1)),0,0,COUNTIF($A:$A,$B$1 & "*"),1)
Now, whenever you select a letter from the list in B1 the Dynamic Named Range (AlphaList) will refers to only the group of cells that Start with the letter chosen. In other words, if the list was names (and of course must be sorted A:Z) and you chose the letter "M" from B1, the Dynamic Named Range AlphaList will only refer to the names that Start with "M".
The uses of this are only limited to your imagination. We selected some of the best robotic pool cleaners available for you, and right after the reviews we answered some questions you may have… plus a buyers Best robot pool vacuum The Dolphin Nautilus CC Plus is one of the best-selling Dolphin pool robots which is no surprise given the outstanding value it offers, balancing performance vs price.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
