# Thread: New article: Defined Names In Excel (97, 2000,xp,2003)

1. ## New article: Defined Names In Excel (97, 2000,xp,2003)

Hi everyone,

About a year ago I published an article in a Dutch magazine about using defined names in Excel.

I thought it was high time to translate that article into English and publish it on my site.

Here's the introduction of the article:

Defined Names In Excel

In Excel you can give a range of cells a name. When you do that, you can use this name instead of the address to point to that range. In general it is easier to remember a name than a range address of the start and end of a range of cells.

Using names has a couple of advantages: ranges are easier to find, formulas are simpler to understand and maintenance of the spreadsheet model becomes more reliable.

Furthermore, some thingsin Excel can only be achieved bij using defined names. But much more is possible with defined names than just naming a range of cells. Because you can also put a formula into a name, a whole world of possibilities opens up!

Even more so, since you can also put the old Excel 4 macro functions to use in a defined name. This enables you to do things normally only possible using VBA.

And here's the TOC:

Introduction
How To Define Range Names
How To Use Range Names
Absolute And Relative Addressing
The Context Of Names
Special Names
A Step Further: A Formula In A Defined Name
Dynamic Names
Another Step Further: XL4 Macro Functions In Names
Passing Arguments To A Defined Name Formula
Bugs in Excel's Name Object
Conclusion

Go and read it, then tell me what you think of it!

2. ## Re: New article: Defined Names In Excel (97, 2000,xp,2003)

Very useful! Thanks again! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

3. ## Re: New article: Defined Names In Excel (97, 2000,xp,2003)

An excellent article. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

One trick you don't mention, that I have seen come up on occasion, is to use named ranges to compare the contents of 2 sheets:
[I figure you have seen this, but for those who have not, some details]
Goto cell A1 of Sheet2
insert-name -define
Name:Compare
Refersto:
=Sheet2!A1 {Note the lack of "\$"}
<ok>
Now goto the other sheet that you want to compare the cells in sheet2 to
Select the range of cells (a1: whatever)
format- conditional format...
formula is:
=A1<>Compare
[format...]
patterns(tab) - choose a color
<ok><ok>

Now any cell in this sheet that has a different value than the corresponding cell in Sheet2 will be highlighted...

Steve

4. ## Re: New article: Defined Names In Excel (97, 2000,xp,2003)

Very nice, once again! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

The only thing I might be able to contribute (from personal (in)experience) is one of the possible "gotchas" with dynamic ranges, as discussed in DAO Error with dynamic range (2000 sp-3/ 98SE).

Alan

5. ## Re: New article: Defined Names In Excel (97, 2000,xp,2003)

Nice example Steve. I didn't include too many examples of using defined names, I just didn't know where to start or stop <g>.

6. ## Re: New article: Defined Names In Excel (97, 2000,xp,2003)

Yes, I read that thread. Dynamic names are indeed a problem when you need to get at named ranges using "other" applications than Excel.

7. ## Re: New article: Defined Names In Excel (97, 2000,xp,2003)

I think more examples of "defined formulas" as opposed to "defined ranges" are more important. I think the typical view of these as "named ranges", though in actuality I think that is a misnomer. There is not (at least in my mind) really a distinction between "named ranges" and "named formulas", all are named formulas (some just refer to a range).

I think this "leap" into thinking of them as "formulas" is the biggest "problem" and barrier with people using them as anything much more than "named ranges", and helping gett past this barrier would be the most benefical to expand their usefulness to the most people...

Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

Steve

8. ## Re: New article: Defined Names In Excel (97, 2000,xp,2003)

That and the fact the Excel's Define Name interface is so poor.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•