Microsoft Office Tutorials and References
In Depth Information
Potential Problems with Names
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 contains 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 remember 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.
 
Search JabSto ::




Custom Search