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

Showing last 8 comments of 23 in total (Show All Comments):

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.

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.