Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Defined Names > Conclusion
Deze pagina in het Nederlands

Range Names In Excel

Conclusion

This article is focused on giving insight in the power of range names in Excel. Starting off with simply naming a range of cells, continued with the powerful option to put a formula in a name and topped off with showing how to put XLM macro functions to use in defined names, enabling you to do things you can normally only achieve using VBA.

By making proper use of defined names, you can build powerful and easy to maintain Excel solutions.

Name Manager

Creating and editing Range Names in Excel is much easier if you start using the Name Manager!

Links

There are various sites that have extensive information about Excel's capability to define a name that refers to a range of cells or a formula. An instructive one is:

Chip Pearson (English)


 


Comments

All comments about this page:


Comment by: Jan Karel Pieterse (3/8/2006 11:54:06 AM)

Dear Reader,

I invite you to comment to this page. It will help me to improve its quality!

Thanks.

Jan Karel Pieterse

 


Comment by: Dave Docherty (3/18/2006 3:42:30 PM)

Thank you for the useful description of your findings with Excel. Dave

 


Comment by: Graham (3/29/2006 1:51:55 PM)

Thanks for a very clear and useful intro to names. I'm sure a large number of people will find this very valuable.

I have a question, though, about referring to named ranges in VBA.

If I select Sheet1, cell A1 and type "MyName" into the name box, I create a global name that refers to Sheet1!$A$1.

Logically I would have thought that this would be a property of the Workbook object, and that I would refer to it as:
ThisWorkbook.Range('MyRange")

But this doesn't work because, I think, the Workbook object doesn't have a Range property.

So one option is to refer to the range including its sheet name:
Sheets("Sheet1").Range("MyRange")
This works, but it seems redundant to have to refer to Sheet1 again since this is already in the definition of the name.

Another option is to use the Application object:
Application.Range("MyRange"), which also works, and is something I use most of the time.

Which option do you use? Lately I've been wondering why no-one else seems to use the second approach. I wonder if this has something to do with having multiple workbooks open?

Is this something you would care to shed some light on?

Thanks

Graham

 


Comment by: Jan Karel Pieterse (3/30/2006 2:01:29 AM)

Hi Graham,

Thanks for your compliments.

If I want to refer to a name, I never use Application.Range, because that will refer to a range on the active sheet/workbook.

I tend to use:

Workbooks("mybook.xls").Names("MyName").RefersToRange

To refer to a global named range in mybook.xls.

If I want a local name, I use

Workbooks("mybook.xls").Worksheets("mysheet").Range("myName")

 


Comment by: Graham Long (3/30/2006 1:03:04 PM)

Well, I never knew that. I knew there had to be a better way. Thanks!

Graham

 


Comment by: Anant Basant (2/3/2007 8:24:27 AM)

A beautiful article. Truly amazing, particularly the information about using XLM functions through defined names. I request you to please keep writing such articles for our benefit.

 


Comment by: Armando Gutierrez (5/1/2007 10:56:15 AM)

FANTASTIC, explanation, Im from Colombia South America.
I was wonder to found this interesting solutions and how you explain this Very nice. sometimes I have a question may I using this way?? regards --AG

 


Comment by: Jan Karel Pieterse (5/1/2007 11:12:37 AM)

Hi Armadno,

Thanks!

If you want to ask questions, check out the newsgroup links on my <a target="_blank" href="http://www.jkp-ads.com/links.htm">links page</a>

 


Comment by: Danny (11/8/2007 4:49:30 AM)

Thank you for your very impressive introduction to Excel defined names. There were several pieces of information that were quite new to me and very powerful: for instance that you can define names local to the sheet *and* the names are copied appropriately when you copy the sheet.
One thing I've wondered about that you didn't address: exactly how does Excel force a column to produce a single value when needed? I have created user-defined functions that expect a single value and work fine on named columns, but produce a DEF? error when I try to run "Evaluate formula".

 


Comment by: Jan Karel Pieterse (11/10/2007 8:12:06 AM)

Anytime you enter a formula that should return an array in a single cell it will either produce #Value! or just return the first value of the array.
A range name always returns an array if the formula in there is an array, you don't have to "control+shift+enter" like with a cell.

 


Comment by: Phil (4/4/2008 5:59:26 AM)

Thanks so much for this wonderfull explanation. Sharing my files with my colleagues was a kind a nightmare when trying to explain formulas and links. It is quite a lot easier now!

Kind Regards

Phil

 


Comment by: Pavan (7/31/2008 10:51:02 PM)

hi,
Great job. You had Explained in the most simple manner about define names. I never
found myself reading and understanding english this much simple. I thank god for
getting this link. Frustated to find about the define names even in their(Office)
default help file.

Thank you,
Pavan :)

 


Comment by: Alex (8/16/2008 9:28:20 AM)

Your blog is interesting!

Keep up the good work!

 


Comment by: Rai (9/3/2008 2:05:10 PM)

Hi great link with awesome information.

I have a question:
I have several sheets in my file and there are few values propogated in number of
sheets. For this I have defined them as 'NAMES' using the Insert-Names-define
command. Now I can simply used these anywhere in the sheets. I have also written a
macro for a chart title which contains this variable but I am not sure how to print
them out. can some one help. the VB script looks like:


Code:
Selection.Characters.Text = "Effects of " + compound + "on the human body"
    With Selection.Characters(Start:=1, Length:=69).Fontcompound has been
previously defined as : compound = AGR

I want the macro to print AGR wherever I have compound.

Thanks
Rai

 


Comment by: Jan Karel Pieterse (9/4/2008 2:04:42 AM)

Hi Rai,

If "compound" is a range name:
Selection.Characters.Text = "Effects of " + Range("compound").Value + "on the human
body"

 


Comment by: John (7/6/2010 1:50:45 AM)

I wish to use the contents of a cell (say I type in July) to use a named range in a Vlookup formula.

I have a sheet for each month of the year and have named ranges for each month July, August, etc and now wish to allow a user to select a month (from a drop dowm menu)and have the formula return result.

I have tried Cell("contents",cell) but the Vlookup command rejects it.



 


Comment by: Jan Karel Pieterse (7/6/2010 4:55:54 AM)

Hi John,

Have a look at the INDIRECT worksheet function.

 


Comment by: hatr (5/9/2011 4:49:07 AM)

Hi,

Thank you for the good explanation. Names are essential for designing well organized and reusable spreadsheets.

Unfortunately I've found another shortcoming of Excel's naming system, so severe that people should know about:
Excel slows down very significantly if you name whole columns and reference them in other columns. There doesn't have to be much data for this to happen, nor do you have to do complicated things. My theory is that Excel idiotically allocates RAM for the whole column when we name it even though most it is empty. Now every time you do something to your spreadsheet, for example deleting a little line, it copies all the empty values for all 2^32 lines senselessly around in your RAM which takes loooong even on new computers. I think people should know. BTW, you have to be patient, Excel will calm down after a while, say 10 minutes of fast computers.

 


Comment by: sirplus (10/27/2011 4:05:31 AM)

I have a workbook with 2 names defined with the same name
One is a Global "RngSales" refering to a sheet "RV" and the other
is local to a Sheet Named "Builders" which is the first sheet in the
WorkBook.

I want to use the Global version but don't seem to be able to reference it

In the immediate window
?Workbooks("Sales Summary11-12.xlsm").Names("RngSales").RefersTo
Gives

=Builders!$A$6 (Which is the local version)
I called the Global Name belonging to the workbook & so expected was
=RV!$A$6

I would have expected the result for
?WorkSheets("Builders").Names("RngSales").RefersTo

=Builders!$A$6 (Which it is)

Is this behavior an extension of the bug you wrote about in the section
Global And Local Names Sharing The Same Name

Or have I discovered a new bug

PS
They appear correct in name manager

 


Comment by: Jan Karel Pieterse (10/27/2011 4:13:32 AM)

Hi sirplus,

This is a good example of the bug I described indeed.
Strange enough, I get the refersto of the *Global* version of the name, whichever worksheet I select.

 


Comment by: sirplus (10/27/2011 6:33:53 AM)

very strange
I get the local version no matter what I do.
So by MS definition its not repeatable and therfore not a bug???
Hang on
I can repeat my version & you yours
LOL
Lets both submit and see whose gets fixed first

 


Comment by: sirplus (10/27/2011 6:48:12 AM)

Whichever appears first in the name manager seems to be the one Excel returns..

 


Comment by: Jan Karel Pieterse (10/27/2011 8:23:50 AM)

Hi sirplus,

You're right!

In both Excel 2003 and 2010, what this line returns:

ThisWorkbook.Names("TheName").RefersTo

depends on the position of the worksheets. If the sheet with the locally defined name is the first sheet, then you get the local refersto. If the "local sheet" is NOT the first sheet, you always get the global one.

EXCEPT if you have activated a worksheet which comes before either of the two, which is what our NAme Manager does.

 


Comment by: Pedro (6/6/2012 5:49:53 AM)

Hello
I need a macro to call the Define Name dialog box and wait until de name is input.
The macros allready recorded do not keep the box opened.
Is it possible to send urgent help regarding the subject
Thank you
ptitob@mail.telepac.pt

 


Comment by: Jan Karel Pieterse (6/6/2012 7:20:06 AM)

Hi pedro,

This line of code opens the dialog:

application.Dialogs(xlDialogDefineName).Show

 


Comment by: Howard (11/3/2012 1:17:31 AM)

The articles comment that range names may obscure references to cells in other workbooks is a serious drawback to using range names.

Example: WorkbookA has two sheets, Sheet1 and Sheet2. A range name "Stuff" is created to refer to cell A1 of Sheet1. A formula in cell B2 on Sheet2 then references that range name "=Stuff+1". Sheet2 is then moved to another workbook, WorkbookB and WorkbookA is then closed. Cell B2 in WorkbookB still includes the formula "=Stuff+1" instead of ='WorkbookA'!Stuff+1. It is now much more difficult to track down references to other workbooks.

Is there anyway to force excel to fully qualify the references as ='WorkbookA'!Stuff+1?

 


Comment by: Jan Karel PIeterse (11/3/2012 8:09:47 PM)

Hi Howard,

No, only by typing the reference yourself.

Copying worksheet tabs is not something Excel handles very well, you drag all sorts of rubbish with it from workbook A to Workbook B, like range names, but also styles.

I recommend not to copy worksheet tabs. Instead, create a new tab on the other workbook and copy the cells (formulas). After copying, ensure there are no unwanted external references to sheets of the original workbook in your target workbook.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].