How to Get the Option to Use Existing Name Range in Excel Workbook Instead of Properating Again

Use the Name Manager dialog box to work with all the defined names and table names in a workbook. For instance, y'all may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You lot can also sort and filter the list of names, and easily add together, alter, or delete names from one location.

To open the Name Manager dialog box, on the Formulas tab, in the Divers Names group, click Proper name Manager.

The Proper name Manager dialog box displays the following information about each name in a list box:

Cavalcade Name	Description
Name	One of the following: A divers name, which is indicated by a defined name icon. A table name, which is indicated by a table proper name icon. Notation:A table name is the name for an Excel tabular array, which is a collection of information most a particular field of study stored in records (rows) and fields (columns). Excel creates a default Excel tabular array name of Table1, Table2, and so on, each time you insert an Excel table. You lot tin can alter a table'south proper name to make information technology more meaningful. For more than information about Excel tables, see Using structured references with Excel tables.
Value	The electric current value of the proper name, such as the results of a formula, a cord abiding, a cell range, an error, an array of values, or a placeholder if the formula cannot be evaluated. The following are representative examples: "this is my string abiding" 3.1459 {2003;12,2002;23,;2001,18} #REF! {...}
Refers To	The current reference for the name. The following are representative examples: =Sheet1!$A$iii =8.3 =HR!$A$1:$Z$345 =SUM(Sheet1!A1,Sheet2!B2)
Scope	A worksheet name, if the scope is the local worksheet level. "Workbook," if the scope is the global workbook level. This is the default option.
Comment	Additional data about the name up to 255 characters. The following are representative examples: This value volition expire on May 2, 2007. Don't delete! Critical name! Based on the ISO certification exam numbers.
Refers to:	The reference for the selected proper name. You lot tin apace edit the range of a proper noun by modifying the details in the Refers to box. After making the alter you lot tin click Commit to save changes, or click Cancel to discard your changes.

Notes:

• You cannot utilize the Name Managing director dialog box while yous are changing the contents of a prison cell.

• The Name Director dialog box does not display names defined in Visual Basic for Applications (VBA), or hidden names (the Visible property of the proper name is set up to False).

1. On the Formulas tab, in the Defined Names grouping, click Define Proper noun.

2. In the New Name dialog box, in the Name box, type the proper noun you want to apply for your reference.

   Annotation:Names can exist up to 255 characters in length.

3. The scope automatically defaults to Workbook. To modify the name'south telescopic, in the Scope drop-down list box, select the proper noun of a worksheet.

4. Optionally, in the Comment box, enter a descriptive comment up to 255 characters.

5. In the Refers to box, practise one of the following:

   • Click Plummet Dialog (which temporarily shrinks the dialog box), select the cells on the worksheet, and and so click Expand Dialog .

   • To enter a abiding, type = (equal sign) and then type the constant value.

   • To enter a formula, type = and then blazon the formula.
     

     Tips:

     • Be careful virtually using absolute or relative references in your formula. If y'all create the reference by clicking on the cell you lot want to refer to, Excel will create an accented reference, such equally "Sheet1!$B$i". If yous type a reference, such as "B1", it is a relative reference. If your active prison cell is A1 when yous define the proper name, and so the reference to "B1" actually means "the cell in the side by side cavalcade". If you lot apply the defined name in a formula in a cell, the reference volition be to the cell in the adjacent column relative to where you lot enter the formula. For example, if you lot enter the formula in C10, the reference would be D10, and not B1.

     • More than information - Switch between relative, accented, and mixed references

6. To cease and return to the worksheet, click OK.

Note:To make the New Proper noun dialog box wider or longer, click and drag the grip handle at the bottom.

If you modify a defined proper name or table proper name, all uses of that name in the workbook are as well inverse.

1. On the Formulas tab, in the Divers Names group, click Name Manager.

2. In the Name Manager dialog box, double-click the proper name you want to edit, or, click the proper noun that you want to change, and then click Edit.

3. In the Edit Proper name dialog box, in the Name box, blazon the new name for the reference.

4. In the Refers to box, alter the reference, and and then click OK.

5. In the Proper noun Manager dialog box, in the Refers to box, change the cell, formula, or constant represented by the name.

1. On the Formulas tab, in the Defined Names grouping, click Name Manager.

2. In the Proper noun Director dialog box, click the name that yous want to change.

3. Select one or more names by doing one of the following:

   • To select a proper noun, click it.

   • To select more than 1 proper name in a face-to-face group, click and elevate the names, or press SHIFT and click the mouse button for each name in the group.

   • To select more than 1 proper name in a noncontiguous grouping, press CTRL and click the mouse button for each name in the grouping.

4. Click Delete.

5. Click OK to confirm the deletion.

Use the commands in the Filter drop-downwards list to quickly display a subset of names. Selecting each command toggles the filter operation on or off, making it easy to combine or remove dissimilar filter operations to become the results you want.

You can filter from the post-obit options:

Select	To
Names Scoped To Worksheet	Display only those names that are local to a worksheet.
Names Scoped To Workbook	Brandish just those names that are global to a workbook.
Names With Errors	Display simply those names with values containing errors (such every bit #REF, #VALUE, or #NAME).
Names Without Errors	Display only those names with values that do non contain errors.
Defined Names	Display just names divers by you or by Excel, such equally a print expanse.
Table Names	Display only tabular array names.

• To sort the list of names in ascending or descending order, click the column header.

• To automatically size the cavalcade to fit the longest value in that column, double-click the right side of the cavalcade header.

Need more help?

You tin always inquire an expert in the Excel Tech Customs or become support in the Answers community.

See Also

Why am I seeing the Name Conflict dialog box in Excel?

Create a named range in Excel

Insert a named range into a formula in Excel

Define and employ names in formulas

riverasesom2000.blogspot.com

Source: https://support.microsoft.com/en-us/office/use-the-name-manager-in-excel-4d8c4c2b-9f7d-44e3-a3b4-9f61bd5c64e4

Share this post