The information in this article applies to:
- Microsoft Excel for the Macintosh, versions 2.2, 3.0, and 4.0
SUMMARY
In Microsoft Excel for the Macintosh, there is no direct way to copy
defined names from one document to another.
To copy a large list of defined names to a new document, do either of
the following (note that you cannot copy the defined names Database,
Criteria, Print Area, and Print Titles).
Workaround 1
- Activate the worksheet containing the defined names to be copied.
- Activate a cell in a blank area of the worksheet.
- From the Formula menu, choose Paste Name.
- Choose the Paste List button.
This pastes the defined names and their cell references onto the
active worksheet.
- Select the cells containing the cell references (the second
column of the paste region).
- Type the following and press OPTION+ENTER
="@@@"&Cellref
where "@@@" is any character or string of characters and Cellref
is the cell reference one cell to the left of the first cell in
the selection. This fills the formula down the selection.
For example, if the first defined name was pasted into cell D1,
and D1 and E1 contain the following
Def_Name =$A$1:$A$10
then =$A$1:$A$10 will be replaced with ="@@@"&D1.
- With the cells still selected, choose Copy from the Edit menu.
- From the Edit menu, choose Paste Special. Select the Values
option. Click OK. This will enter @@@name into each cell in the
selected range, where "name" is the respective defined name.
- From the Formula menu, choose Replace. Enter "@@@" (without
quotation marks) in the Replace box, and "=" (without quotation
marks) in the With box. Choose the Replace All button.
NOTE: At this point, some cells may contain the #VALUE! error
value. If a defined name refers to a range of cells, the new
formula created through these steps will return a #VALUE error
value. You can ignore this value.
- From the Edit menu, choose Copy.
- Switch to the destination worksheet. From the Edit menu, choose
Paste.
The defined names are copied to the destination worksheet. To verify
that the cell references are the same, choose Define Name from the
Formula menu.
Workaround 2
In the case of a large Print Area that contains multiple nonadjacent
selections, and other instances where the named range formulas consist
of complex arrays, it can be useful to transfer that information to
the new worksheet without having to retype the information.
- From the Formula menu, choose Define Name.
- From the list of defined names, select Print_Area.
- Press TAB once to slect the Refers To box.
- From the Edit menu, choose Copy.
- Choose OK or Close to close the Define Name dialog box.
- Activate the new worksheet.
- From the Formula menu, choose Define Name.
- In the Name box, type "Print_Area" (without the quotation marks).
- Press TAB once and choose Paste from the Edit menu and choose OK.
|