<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**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 l**ast 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**

**
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

**Some of our more popular products are below...**

**FREE Excel Help **