|
This page describes the use of Named Ranges in Excel.
What Are Named Ranges?
Named Ranges are a powerful tool in Excel that allows you to assign a meaningful name
to a single cell or a range of cells. For example, you can assign the name "TaxRate" to cell C1 and
then use the name "TaxRate" anytime you would
normally use the cell C1, such as =A5*TaxRate.
There are 3 advantages to using Named Ranges:
- Formulas are more readable and meaningful. A formula like =A5*TaxRate
is more meaningful to you when you are working with a complex worksheet.
- Named Ranges, by default, always use absolute cell references. Therefore, you
don't have to worry about address translation, which occurs with relative cell references,
when you Copy/Paste or Fill Down/Right cell ranges. (For more information about
absolute and relative cell references, click here.)
- Named Ranges make it easier to create well organized and attractive workbooks. You can
use a named reference, rather than a cell address, in formulas, and then define that name
to a specific cell after you've designed the workbook. With Named Ranges, you won't
have to edit and change the dependent formulas. Just change the reference of the
name.
Valid Range Names
A range name can contain letters, numbers, and underscores, but not spaces or special
punctuation characters. Moreover, it cannot be the same as a normal cell
reference. For example, "AA10" is not a valid range name because
"AA10" is the name of a normal cell reference (row 10, column "AA").
While it is perfectly legal as far as Excel is concerned to use a Name with the same name as worksheet
(e.g., you can have the odd situation of having the name 'Sheet1' refer to a cell on the worksheet 'Sheet2'),
I would strong recommend against this. It will lead only to confusion.
Adding And Deleting Named Ranges
To create a new Named Range from Excel, use the following procedure:
Select the cell or range of cell that you want to assign a name to. Go to the Insert
menu, select the Name menu item, and type your name in the text box.
To delete a named range, go to the insert menu, select the Name menu item, then select the
name from this list, and click the Delete button.
See below for VBA procedures for adding and deleting
names.
Naming Formulas
A Named Range does not have to refer to a cell or a range of cells. It can refer
to a formula. However, all of the arguments to the formula are "static".
They do not depend on the cell from which the name is invoked. You should
always use absolute cell references in a named formula; otherwise the result can be very
confusing.
For example, if you frequently use the formula =SUM($A$1:$A$10),
you can create a named formula called MySum, referring to the formula =SUM($A$1:$A$10). Then, you can use the name anywhere
you'd normally use the formula: =MySum/2
Another advantage is that you can overcome Excel's limitation on eight nested
functions. See the Nested IFs page for more details.
Dynamic Ranges
It is often useful to create a name that refers to a range of cells, where the range
depends on the content of the cells. For example, you may want a name that refers to
the first N non-blank entries in column A. Excel's Name tool allows you to do
this. For example, creating a name called DynaRange,
referring to
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
If the first 20 rows of column A contain data (and the rest are blank), DynaRange will refer to the range A1:A20
See the on-line help for the =OFFSET function for a
description of the arguments. Setting the Width argument to 2 will allow us to use
this name in a =VLOOKUP function
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)
Then, call VLOOKUP with the DynaRange
argument for the lookup range:
=VLOOKUP(C1, DynaRange, 2)
As data is added to columns A and B, the range search by VLOOKUP
will extend to include the new data.
Download a workbook illustrating dynamic ranges.
Defined Names In Data Validation And
Conditional Formatting
When you use the Data Validation tool, you can restrict valid inputs to the
values in another range. However, the Data Validation dialog won't allow
you to specify cells on another worksheet. But using a defined name, you
can get around this limitation. Simply give the range containing the valid
values a name, such as ValidRange, and then in the Data Validation dialog, enter
=ValidRange as the Source for the validation list. Data Validation won't
object, and everything will work as you expect.
You can use the same technique with Conditional Formatting. The
Conditional Formatting objects when you try to use a reference to a cell or
range on another worksheet. Just name the source cell or range, and use
the defined name in the Conditional Formatting formulas. CF won't
object, and everything will work as you expect. See the Conditional
Formatting page for more details.
VBA Procedure For Working With Names
Adding A Range Name
You can use the Add method of the Names collection object to add a new
defined name.
ThisWorkbook.Names.Add Name:="NewName",
_
RefersTo:="=$A$1:$C$10", Visible:=True
If you set the Visible property of the name to False, the
name will not be displayed in the Names dialog box, although the name can still
be used normally in VBA procedures and worksheet formulas.
If you add a name that already exists, Excel will replace
the old name with the new name. An error will not be
generated.
Determining Whether A Name Exists
The following VBA procedure will return TRUE if the name
specified in TheName exists, or will return FALSE otherwise.
Function NameExists(TheName As String) As Boolean
On Error Resume Next
NameExists = Len(ThisWorkbook.Names(TheName).Name) <> 0
End Function
Deleting A Name
The Delete method of the Names collection can be used to
delete a name.
ThisWorkbook.Names("NewName").Delete
If the name does not exist, a run time error will
occur.
Determining The Name Of A Cell Or Range
You can use VBA procedures to determine the define named
that contains a given cell or range. You must first decide whether you want to
return a name that refers to some range that intersects with your range, or
whether you want to return the name of the range that refers to exactly the same
range or cell as your range. Below are two VBA procedures to do
this.
Function NameOfParentRange(Rng As Range) As String
Dim Nm As Name
For Each Nm In ThisWorkbook.Names
If Rng.Parent.Name = Nm.RefersToRange.Parent.Name Then
If Not Application.Intersect(Rng, Nm.RefersToRange)
_
Is Nothing Then
NameOfParentRange = Nm.Name
Exit Function
End If
End If
Next Nm
NameOfParentRange = ""
End Function
This procedure will return the name of the range that
intersects with the range specified by the Rng argument. For example, if
the name Range1 refers to A1:A10, and Rng is A5:D5, the function will return
"Range1" because the range A5:D5 intersects with A1:A10.
With this function, Rng does not have be exactly match a defined range -- there
simply must be a non-empty intersection between the two.
If you need an exact match, use the function
below.
Function ExactRangeName(Rng As Range) As String
On Error Resume Next
ExactRangeName = Rng.Name.Name
End Function
With this function, if the name Range1 refers to A1:A10,
the function will return an empty string unless Rng refers to exactly
A1:A10.
|