Range Names in Excel

Content

Introduction

Range names are an important feature of Excel. This article demonstrates many aspects of range names.

In Excel you can name a range of cells. When you do that, you can use this name in your formulas, instead of the address. In general it is easier to remember a name than a range address 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 things in Excel can only be achieved by 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, you can put the old Excel 4 macro functions to use in a defined name. This enables you to do things normally only possible using VBA.

How To Define Range Names

There is a number of ways you can name a range of cells.

The first one is by using the Name box to the immediate left of the formula bar:
The Excel Name box

When the current selected range is already named, the name box will show its name. Otherwise you'll see the address of the active cell in this box. Select the range you want to name and then click in the Name box and type the name. Press Enter to confirm.

There is one drawback to this method. When a name already exists, Excel will not apply the name to the selection. Instead Excel will select the range belonging to the existing definition of the name you typed.

So the Name box can also be used to navigate to an existing name. Just select it from the dropdown list or type it in the box and hit enter.

A fast way to create range names can be used when the headings of a table already represent the names you would like to assign to its rows and/or columns. See the table below:

 Creating names using create from selection

Select the both the tables' content and its headers. Then click the Formulas tab and click "Create from Selection".

You'll see this dialog popup:
 Dialog screen Create Names

By selecting the options shown in that screenshot, seven names are defined in one go:

Name
Refers to:
Or
Apples
=Sheet1!$B$2:$B$4
The three cells below Apples
Pears
=Sheet1!$C$2:$C$4
The three cells below Pears
Bananas
=Sheet1!$D$2:$D$4
The three cells below Bananas
Europe
=Sheet1!$B$2:$D$2
The three cells to the right of Europe
Asia
=Sheet1!$B$3:$D$3
The three cells to the right of Asia
America
=Sheet1!$B$4:$D$4
The three cells to the right of America
TurnOver
=Sheet1!$B$2:$D$4
All cells in the table, except the headers

The third way to define a name through the user interface is by selecting "Name Manager" from the Formulas tab (keyboard short-cut: control+F3) and clicking the "New" button. This method will be used throughout the rest of this article.

Excel Name Manager dialog

How To Use Range Names

Using the table I showed earlier, I'll demonstrate some options with names. To calculate the total of Apples sold, one would normally use this formula: =SUM(B2:B4). After defining names you can simply type: =SUM(Apples). It is evident this is easier to understand.

Using a name in a formula

Another interesting thing is that names are always arrays. If you select three cells below each other and type =Apples, (followed by control-shift-enter in older versions of Excel), you get:

A name is an array
you will get the values of the three cells Apples refers to.

But there is more. When you would like to know the apples sold in Europe (1000) you can type:

=Apples Europe

By separating the two names with a space (also called the intersect operator) you indicate you want to get the intersection of these two named ranges. Since in this case this is a single cell, the formula returns a single number.

Names can be used in all areas of Excel that accept a range of cells as an argument.

One example is Data Validation using the List option (See below). Without explaining this powerful Excel feature I show you that using a defined name enables you to use a list source that is not on the same worksheet as the one you want to set the Validation on. Normally, if you would try to enter a range of cells that is on another worksheet into the Source box, versions of Excel prior to Excel 2010 show an error message:

Setting up data validation using a reference to another worksheet

In order to achieve this, name the range you want to use as the source (e.g. Apples) and use this formula in the Source box:

=Apples

The Validation will now be accepted by all versions of Excel.

Absolute And Relative Addressing

When you define a name, Excel defaults to using absolute cell references: Sheet1!$A$1. The dollar signs indicate that the address should be interpreted absolutely, independent of the cell that calls the name.

You can also use relative references however, giving you interesting possibilities.

If for example you select cell C3 on Sheet2 and define a name as =Sheet2!A1, Then this name will point to a cell two columns to the left and two rows up from the cell the name is used in.

Defining a relative range name

So the formula =Relative in cell C14 on Sheet2 will point to cell A12:

Using a relative range name in a formula

The Context Of Names

When you define a range name using the methods described so far, you will always get a defined name, which is visible throughout an entire workbook.

In general this kind of name is referred to as a global name. The workbook is their parent object. Names can also be defined local to a worksheet. By using the Scope drop-down, you can create a local name:

The scope of a name

If a name has both a global and a local version, problems may arise.

On a worksheet with a local name it is not possible to use the global name, Excel will always use the local name. On other worksheets, Excel will default to the global name, unless you precede it with the worksheet name:

=Sheet3!LocalName

When one creates a copy of a worksheet to which one or more global names refer, Excel will automatically create copies of those names local to the copied worksheet. Name Manager shows the result of copying Sheet1:

Name Manager showing copied names

Note, that the each name has acquired a local copy which has 'Sheet1 (2)' as its scope.

Special Names

Excel also uses defined names for built-in functions. The table below lists a few:

Used option
Name
Print area
Sheet1!Print_Area
Print Titles
Sheet1!Print_Titles
Advanced Filter Criteria
Sheet1!Criteria

It is not wise to remove or change this kind of names using the Define Name dialog (or by other means), unless you know exactly what you are doing. It is better to let Excel handle those names.

A Step Further: A Formula In A Defined Name

Up until now, the names in this article referred to ranges of cells. In reality, a defined name in fact just holds a formula (like in a cell!) and you can use any of Excel's extensive list of worksheet functions in names. This opens up a world of possibilities!

Have a look at the screen shot below. Here a name called ColumnSum has been defined, using cell B5 as the anchor cell. The principle of using relative references has been used here as well, as shown in a previous section. The formula for ColumnSum is:

=SUM(B$2:B4)

Or the sum of all cells as from row 2 down to the row immediately above the cell in which the name has been applied to, restricted to the same column as the name is used in.

Sum of cells above using a relative name

So when you enter =ColumnSum into cell B5, in reality Excel will compute =SUM(B2:B4) for you:
Sum of cells above using a relative name
Similarly, when entered into cell D20, Excel calculates =SUM(D2:D19). The advantage of using this technique shows especially with complex formulas. Should you need to change anything to the logic of the formula, then it suffices to just change the formula in the defined name as opposed to having to update multiple cells.

Another nicety about named formulas, is that they behave as array formulas by default.

Dynamic Names

Oftentimes you need to calculate (sub)totals of ranges to which data is to be added at a later stage. It would be nice however if you wouldn't need to adjust references in formulas every time data is added to the file, it is more practical to let Excel do this kind of housekeeping for you.

By using the OFFSET worksheet function the exact range containing data can be made dynamic. The OFFSET function has these arguments:

=OFFSET(StartingAddress,RowsDown,ColumnsToRight,NuberOfRows,NumberOfColumns)

Suppose you have a table like this:

Setting up a dynamic range name
 and you want a range that refers to the apples column, adjusting itself for the number of regions filled in in column A.

Using B2 as the starting address, go down 0 rows and 0 rows to the right. Then count the number of rows in column A (for this you can use the COUNTA function, which counts all filled cells in its range argument) and finally, use a single column. The end result should look like this:

=OFFSET(Sheet5!$B$2,0,0,COUNTA(Sheet5!$A:$A)-1;1)

Because you don't want to include the row headings in the count, 1 is subtracted from the COUNTA result.

This formula has been defined as ApplesDynamic:

Setting up a dynamic range name
Definition of a dynamic name (note the use of the semicolon list separator in the formula, most users have to use the comma instead)!

Because column A has been used to count the number of rows that are filled, the actual range this new dynamic name refers to will always be determined by how many cells in column A actually contain information. Because of this, you cannot enter any information in cells below the current table, or leave rows empty inside the table: The dynamic range will be off by the number of empty cells or by the number of extra used cells below the table.

When you now enter a new area into the table (like Australia in cell A5), the name ApplesDynamic will automatically refer to the range B2:B5. So when a sales amount for Australian Apples is entered, it will automatically be used in any formulas that use ApplesDynamic. To ensure the ranges next to the ApplesDynamic range are dynamic as well you can again apply the OFFSET function. This time it will be derived as an offset from the ApplesDynamic range: PearsDynamic will be defined as:

=OFFSET(ApplesDynamic,0,1)

In other words: PearsDynamic is simply ApplesDynamic, but shifted one column to the right. The last one is defined using the same method:

BananasDynamic: =OFFSET(ApplesDynamic,0,2)

Finally, you can make the data area of the entire table dynamic as well, so addition of fruits is accounted for automatically:

"TurnOverDynamic".

=OFFSET(Sheet5!$B$2,0,0,COUNTA(Sheet5!$B:$B)-1, COUNTA(Sheet5!$1:$1)-1)

As you can see, both the number of rows and the number of columns are here determined using the COUNTA function.

Whether or not the names have been defined properly is easy to check. Hit F5 (Goto) and type a name to check into the Reference textbox and hit OK. Excel will select the computed area the name currently refers to.

A practical example of using dynamic names in combination with a chart is shown in  autochrt.zip.

Although dynamic names can be useful, it is better to convert your range to a table and use table referencing

Another Step Further: XL4 Macro Functions In Names

It is not widely known that one can use XLM (no, not XML!) macro functions (these go way back to Excel version 4) in defined names. This enables you to do things you normally can only do using VBA.

One important warning has to be made though. When you copy a cell that uses any name containing XLM macro functions to another worksheet, Excel 97 and Excel 2000 will crash! Copying the formula itself from the formula bar (by selecting the formula and hitting control-c) will be no problem however.

Example 1: Creating A List Of Files In A Worksheet.

You can do this by using the XL4 FILES macro function, applied in a name called "Files":

=FILES(Sheet6!$C$2).

On Sheet6 in cell C2 you can enter a path and filename (including wildcards) like "c:\*.*". In any column, starting on row 1 the following formula will show the files found in that folder:

=INDEX(Files,ROW())

Copy this cell downwards until the formula returns #REF!. You can now easily update this list to show another folder by simply typing the foldername into cell C2.

The XLM FILES function in a range name

Example 2: Reading the interior color of a cell.

With the function GET.CELL you can retrieve all kinds of information about a cell, amongst others the colorindex of the interior (fill color) of the cell. To achieve this, define this name (after selecting cell B2 on Sheet7): Name: CellColor, RefersTo:

=GET.CELL(63,Sheet7!A2)+NOW()*0

The addition of NOW()*0 ensures that the name is recalculated every time Excel recalculates. The argument 63 tells the GET.CELL function you want the interior color:

The GET.CELL function in a range name

Note that you can use a relative reference so the name will refer to a cell relative to the cell it is used in. In this example, the name points to the cell to the immediate left of the cell you will use the name in. Because changing the color of a cells interior will not fire a recalculation, changing formatting wil not automatically change the formula results in this example.

Using this new name enables one to e.g. sum all blue colored cells in a range:

Examples of using XLM functions in range names

In this example, the ranges C16 and E16:E25 contain the formula =CellColor. Cell D26 contains the formula: =SUMIF(E16:E25,"=" & C16,D16:D25)

Example 3: Adding just the displayed digits.

When you have set a particular number format to a cell to show e.g. just 2 decimals after the dot, rounding "mistakes" may show up in your spreadsheet. This is caused by the fact that Excel uses all digits of the number in its cells to do the math and not just the digits shown to you.  The GET.CELL function can extract the value shown in the cell (create this name whilst in cell C2):

Name: NumberInCell RefersTo: =GET.CELL(53,Sheet8!A2)+NOW()*0

Examples of using XLM functions in range names

You can clearly see that the result in cell C9 differs from the result in B9.  Of course you could also use the ROUND worksheet function to achieve a similar result, but the advantage of this method is that when you change the number formats of the cells to be summed, the result will reflect that (after a recalc of course).

Example 4: Referring to the previous and next worksheet.

Sometimes it is useful to have a way to have a formula point to the sheet to the immediate left, regardless what sheet is moved or inserted there. When this formula is copied from Sheet2 to Sheet3, the copy will refer to Sheet2 instead of to Sheet1 (normally the sheet reference does not change when copying a formula to another sheet). You need a combination of a couple of names to achieve this. First define a name called "AllSheets", referring to:

=GET.WORKBOOK(1+0*NOW())

The result of this name will be (in a workbook named Names02EN.XLS containing Sheet1, Sheet2 and Sheet3):

{[Names02.XLS]Sheet1,[Names02.XLS]Sheet2,[Names02.XLS]Sheet3}

The name of the current worksheet can be extracted using this formula in the defined name called "ThisSheet":

=GET.CELL(32+0*NOW(),INDIRECT(GetRC,FALSE))

The formula INDIRECT(GetRC,FALSE) extracts the cell from which the name has been used. The GetRC name determines what letters Excel is using to determine rows and columns (to make this formula work in any language version of Excel):

Name: GetRC

RefersTo: =SUBSTITUTE(REFTEXT(!$A$1),1,"")

GET.CELL(32,..) yields the name of the worksheet that contains the cell you extracted using the INDIRECT clause shown above, preceded by the filename. On Sheet2 the result of this formula will be: [Names02.XLS]Sheet2

By using the following formula in the name called "PreviousSheet"), you can extract the name of the sheet to the immediate left of the current worksheet:

=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)-1)

The Match function gets the index of the current worksheet. By subtracting 1 you will have the index of the sheet to the immediate left of the current sheet. Finally, the INDEX function gives you the actual name of the shet (again, preceded with the filename). Similarly you can extract the name of the next worksheet (name: "NextSheet"):

=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)+1)

Finally the following formula will get you the value of cell B2 on the previous worksheet:

=INDIRECT("'"&PreviousSheet&"'!"&CELL("address",B2))

And ditto for the next worksheet:

=INDIRECT("'"&NextSheet&"'!"&CELL("address",B2))

The file Arg2Name.zip on the download page gives some examples of the use of XL4 macro functions in names.

If you want to learn what else you can do with these macro functions, download the old help files for here (in PDF format) courtesy MyOnlineTrainingHub.

Passing an argument to a named formula

Note that this chapter is now obsoleted by the new LAMBDA function of Excel.

As shown in the file Arg2Name.zip (see the download page), there is a trick to pass arguments to a defined name formula.

First the named formula called Myref (see below) has to be created, which evaluates the string of the formula of the "active" cell (the cell that calls one of the other named formulas in this example) and extracts an address out of that. In order to use these formulas, a trick is involved. The function that contains the address should look like this:

=IF(ROW(ref),NameOfTheDefinedFormula)

The Myref function finds the "Row(" part in the string of the formula and takes all text between that and the first closing paren to be a valid cell reference. Myref in part is used as an argument to the other functions.

Define Myref as:

=MID(GET.CELL(6,INDIRECT(GetRC,FALSE)),FIND("ROW(",GET.CELL(6, INDIRECT(GetRC,FALSE)))+4,FIND(")",GET.CELL(6,INDIRECT(GetRC,FALSE)))-FIND("ROW(",GET.CELL(6,INDIRECT(GetRC,FALSE)))-4)

Note, that Myref has used the GetRC name demonstrated above.

Here are some examples that may be used in combination with the Myref named formula. Define these names:

IsFormula =GET.CELL(48,INDIRECT(myref)))+0*now()

CellColor =get.cell(63,indirect(MyRef))+0*now()

RowIsHidden =IF(GET.CELL(17,INDIRECT(Myref))=0,TRUE,FALSE)+0*now()

RowHeight =GET.CELL(17,INDIRECT(Myref))+0*NOW()

An example how to use these functions in worksheet cells:

=IF(ROW(D3),CellColor)

Shows the colournumber of the background of cell D3.

=IF(ROW(D3),RowHeight)

Shows the rowheight of cell D3 (in effect of row D).

Bugs in Excel's Name Object

During development of the Name Manager, a number of bugs in Excel's defined name object have been revealed.

From the Name Manager's Manual:

There are some bugs in Excel’s Names collection and Name Object:

RefersToLocal

The RefersToLocal property of a name will not accept formula strings in Excel's UI language. For instance, in a Dutch Excel this code line:

Names("Test").RefersToLocal="=SOM($A$1;$B$1)"

Gives a runtime error, even if the formula is completely correct and identical to a worksheet formula. In the Name Manager a workaround has been used to overcome this problem. The RefersToLocal property ONLY accepts US syntax for formulas and separators.

Showing The "Define Name" Dialog Through VBA Code

When one has an Excel version that is NOT English and one shows the Insert Name dialog from Code, using either:

Application.Dialogs(xlDialogDefineName).Show

or

Application.CommandBars(1).FindControl(ID:=878, recursive:=True).Execute

It always seems to show the dialog with the cell reference in R1C1 (of course Excel is set to A1)!

Names Local To Sheets With Special Characters In Their Name

When one defines a local name on a Sheet named "Test":

Name: Test!test

Refersto: "1"

and renames this sheet to e.g. "Hi! There!" (without the quotes), this name becomes inaccessible for editing and deleting, yielding an invalid name error message. Other strange characters in sheet names (especially "[", "]" and char(3) ) may even cause a crash of Excel when trying to access these local sheet names. A check has been included in the Name Manager to overcome this problem.

Global And Local Names Sharing The Same Name

Accessing a global name whilst the active sheet has an identically named name local to that sheet, will change the properties of the local name and NOT the global name, even if the name is fully qualified with the workbook name. Name Manager bypasses this problem.

Names Who's RefersTo Property Starts With =!

Names with refers-to starting with =! (for example =!$A$1) may give incorrect results when calculation is called from VBA. Such names should (with this example) give you the result of the cell A1 on the sheet the name is used in, but when a recalc is forced through a macro, the name will get the result form the cell on the active sheet! Name manager will show a warning message whenever it finds a name of this type. If you want to have a name that will refer to a fixed cell on whatever sheet the name is used on, use =INDIRECT("A1") in stead of =!$A$1 to avoid this Excel bug.

Other issues with defined names

Auditing problems

Using defined names to make your formulas easier to read has disadvantages too. Because normal cell references have been replaced with names, a spreadsheet may become harder to audit. Especially if hundreds of names have been applied, without any obvious naming convention, auditing such workbooks may become a daunting task.

Copying sheets can create links in names

When one copies a worksheet from one workbook to another, defined names may travel with the copy. All names that refer to the copied sheet directly are copied to the other workbook. But also all defined names used on the copied sheet are copied to the new workbook, but they keep referencing the original file. Thus, a link between the two books is created behind the scenes and mostly without the user realizing this has happened.

As long as one caters for these issues, defined names are an important part of any spreadsheet model and can improve the maintainability of the model too.

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!

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 (8-3-2006 11:54:06) deeplink to this comment

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 (18-3-2006 15:42:30) deeplink to this comment

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


Comment by: Graham (29-3-2006 13:51:55) deeplink to this comment

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 (30-3-2006 02:01:29) deeplink to this comment

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 (30-3-2006 13:03:04) deeplink to this comment

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

Graham


Comment by: Anant Basant (3-2-2007 08:24:27) deeplink to this comment

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 (1-5-2007 10:56:15) deeplink to this comment

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 (1-5-2007 11:12:37) deeplink to this comment

Hi Armadno,

Thanks!

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


Comment by: Danny (8-11-2007 04:49:30) deeplink to this comment

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 (10-11-2007 08:12:06) deeplink to this comment

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 05:59:26) deeplink to this comment

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 (31-7-2008 22:51:02) deeplink to this comment

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 (16-8-2008 09:28:20) deeplink to this comment

Your blog is interesting!

Keep up the good work!


Comment by: Rai (3-9-2008 14:05:10) deeplink to this comment

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 (4-9-2008 02:04:42) deeplink to this comment

Hi Rai,

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


Comment by: John (6-7-2010 01:50:45) deeplink to this comment

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 (6-7-2010 04:55:54) deeplink to this comment

Hi John,

Have a look at the INDIRECT worksheet function.


Comment by: hatr (9-5-2011 04:49:07) deeplink to this comment

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 (27-10-2011 04:05:31) deeplink to this comment

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 (27-10-2011 04:13:32) deeplink to this comment

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 (27-10-2011 06:33:53) deeplink to this comment

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 (27-10-2011 06:48:12) deeplink to this comment

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


Comment by: Jan Karel Pieterse (27-10-2011 08:23:50) deeplink to this comment

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 05:49:53) deeplink to this comment

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 07:20:06) deeplink to this comment

Hi pedro,

This line of code opens the dialog:

application.Dialogs(xlDialogDefineName).Show


Comment by: Howard (3-11-2012 01:17:31) deeplink to this comment

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 (3-11-2012 20:09:47) deeplink to this comment

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.


Comment by: Sandeep (23-5-2022 04:20:00) deeplink to this comment

Excellent, exhaustive article on Range Names.


Comment by: Jan Karel Pieterse (23-5-2022 10:01:00) deeplink to this comment

Thank you Sandeep!


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.




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