Microsoft Office Tutorials and References
In Depth Information
=Sheet1!$C$3:$E$5
Inserting a row or column
When you insert a row above the named range or insert a column to the left of the named range, Excel changes
the range reference to reflect its new address. For example, if you insert a new row 1, MyRange then refers to
=Sheet1!$C$4:$E$6.
If you insert a new row or column within the named range, the named range expands to include the new row or
column. For example, if you insert a new column to the left of column E, MyRange then refers to
=Sheet1!$C$3:$F$5.
Deleting a row or column
When you delete a row above the named range or delete a column to the left of the named range, Excel adjusts
the range reference to reflect its new address. For example, if you delete row 1, MyRange refers to
=Sheet1!$B$3:$D$5.
If you delete a row or column within the named range, the named range adjusts accordingly. For example, if
you delete column D, MyRange then refers to =Sheet1!$C$3:$D$5.
If you delete all rows or all columns that make up a named range, the named range continues to exist, but it con-
tains an error reference. For example, if you delete columns C, D, and E, MyRange then refers to
=Sheet1!#REF!. Any formulas that use the name also return errors.
Cutting and pasting
When you cut and paste an entire named range, Excel changes the reference accordingly. For example, if you
move MyRange to a new location beginning at cell A1, MyRange then refers to =Sheet1!$A$1:$C$3. Cutting
and pasting only a part of a named range does not affect the name's reference.
Potential Problems with Names
Names are great, but they can also cause some problems. This section contains information that you should re-
member when you use names in a workbook.
Name problems when copying sheets
Excel lets you copy a worksheet within the same workbook or to a different workbook. Focus first on copying a
sheet within the same workbook. If the copied sheet contains worksheet-level names, those names will also be
present on the copy of the sheet, adjusted to use the new sheet name. Usually, this is exactly what you want to
happen. However, if the workbook contains a workbook-level name that refers to a cell or range on the sheet
that's copied, that name will also be present on the copied sheet. However, it will be converted to a worksheet-
level name! That is usually not what you want to happen.
Consider a workbook that contains one sheet (Sheet1). This workbook has a workbook-level name ( BookLevel )
for cell A1 and a worksheet-level name ( Sheet1!SheetLevel ) for cell A2. If you make a copy of Sheet1 within
the workbook, the new sheet is named Sheet1 (2). You'll find that, after copying the sheet, the workbook con-
tains four names, as shown in Figure 3-15. The new sheet has two worksheet-level names.
Search JabSto ::




Custom Search