Excel: Copying Defined Names Between Documents

ID: Q63810

The information in this article applies to:

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

  1. Activate the worksheet containing the defined names to be copied.


  2. Activate a cell in a blank area of the worksheet.


  3. From the Formula menu, choose Paste Name.


  4. Choose the Paste List button.

    This pastes the defined names and their cell references onto the active worksheet.


  5. Select the cells containing the cell references (the second column of the paste region).


  6. 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.


  7. With the cells still selected, choose Copy from the Edit menu.


  8. 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.


  9. From the Formula menu, choose Replace. Enter @@@ in the Replace box, and = 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.


  10. From the Edit menu, choose Copy.


  11. 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.

  1. From the Formula menu, choose Define Name.


  2. From the list of defined names, select Print_Area.


  3. Press TAB once to slect the Refers To box.


  4. From the Edit menu, choose Copy.


  5. Choose OK or Close to close the Define Name dialog box.


  6. Activate the new worksheet.


  7. From the Formula menu, choose Define Name.


  8. In the Name box, type Print_Area.


  9. Press TAB once and choose Paste from the Edit menu and choose OK.


Additional query words: 2.20 2.00 4.00 4.00a 


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 21, 1999