Microsoft Office Tutorials and References
In Depth Information
Working with formulas
Workbook-wide vs. worksheet-only names
Names in Excel usually function on a workbook-wide basis. That is, a name you define on
any worksheet is available for use in formulas on any other worksheet. But you can also
create names whose scope is limited to the worksheet—that is, names that are available only
on the worksheet in which you define them. You might want to do this if, for example, you
have a number of worksheets doing similar jobs in the same workbook and you want to use
the same names to accomplish similar tasks on each worksheet. To define a worksheet-only
name, click the Scope drop-down list in the New Name dialog box, and select the name of
the worksheet to which you want to limit the scope of the name.
TROUBLESHOOTING
My old worksheet-level names have changed
In versions of Excel prior to 2007, you created worksheet-level names by preceding the
name (not the cell reference) with the name of the worksheet, followed by an
exclamation point. This no longer works, and it’s easier now anyway, using the Scope options
in the New Name dialog box. If you have existing worksheet-level names in workbooks
you created using previous versions of Excel, they will still work after you import the
workbooks into Excel 2010 or 2013, but Excel modifies the name by removing the old
designation (the worksheet name and exclamation point) and adds the Scope
designation instead.
For example, to define TestSheetName as a worksheet-only name in Sheet1, select the
range you want, click the Define Name button on the Formulas tab, type TestSheetName
in the Name text box, and then select Sheet1 from the Scope drop-down list, as shown in
Figure 12-13.
Figure 12-13 Use the Scope drop-down list to specify a worksheet to which you want to restrict
a name’s usage.
Search JabSto ::




Custom Search