Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Advanced Excel Dynamic Named Ranges

| | Information Helpful? Why Not Donate.

Advanced Excel Dynamic Named Ranges - Expand/Contract Named Ranges

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

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<whatever> 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 A and 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.

<< Back to Excel Formulas Index << Back to Excel Named Ranges

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help