How do names work

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,231
I have a workbook with several worksheets. When I view the names in the workbook in "Sheet1" (Insert Names Define), I see all of the names. Those which relate to "Sheet1" have "Sheet1" on the right hand side. Those which refer to other sheets have white space on the right hand side.

But some of the names refer to other sheets yet have "Sheet1" on the right hand side. Does anyone know A. How this happened and B. What is going on.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can create a sheet level name by preceding it with a reference to the sheet when you create it, eg:

Sheet1!MyName

Sheet level names appear in the list of Names in workbook only when the relevant sheet is active.

Excel uses sheet level names for Print_Area and Print_Titles.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
This usually arises when you copy a worksheet where a "workbook level" name exists; you then have a worksheet-specific level version of the name on the copy. I normally try to get rid of them to avoid confusion - except for the names Criteria and DataBase, which I create for filters, whose scope is never beyond individual worksheets, so I'll let them "live." However, there are other circumstances where the "local" (sheet-specific) cases can be useful.
 

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,231
Thank you Andrew and Gates. Two helpful explanations to something that has bothered me for some time.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
As a side note, you can view and paste and print a list of all the names in the workbook using F3 or ALT+I+N+P

lenze
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,395
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top