For example, if you have created the global name “Profit” and then create another global name called “PROFIT” in the same workbook, the second name will be rejected as names need to be unique, irrespective of capitalisation. Names can contain uppercase and lowercase letters, but Excel does not distinguish between uppercase and lowercase characters in names. There appears to be no limit to the number of names you can define, but a name may contain no more than 255 characters. You cannot use a name that could otherwise be confused as a cell reference for example, “Day1”, as this is already a cell reference (many people have tried!). For example, to enter the name “Cash Flow” you should enter “Cash_Flow” or “Cash.Flow”. Spaces are not allowed, but two words can be joined with an underscore (_) or period (.). Remaining characters in the name can be letters, numbers, periods, and underscore characters. The name string must begin with a letter or underscore character. You will confuse Excel, the end user, and most importantly, yourself! Further, where possible, avoid using range names in (sections of) worksheets that will be copied to other worksheets or workbooks.
It is strongly recommended that you always try to create range names on a workbook scope level only. This will always use the local name if there is a name conflict and cannot be overridden. It is possible to override the local worksheet level for all worksheets in the workbook, except for the first worksheet. This can be circumvented by adding a prefix to the name, eg, rename it “WorkbookFile_Profit” instead. The local worksheet level takes precedence over the global workbook level. To resolve this conflict, Excel uses the name that is defined for the worksheet by default. In this case, there may be a name conflict.
You can even define the same name, such as “Profit”, for the global workbook level, but again this scope is unique. You might do this to ensure that a formula that uses the name “GrossProfit”, for example, is always referencing the same cells at the local worksheet level. Although each name is the same, each name is unique within its scope. For example, you can define a name, such as “Profit”, that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook. However, you can use the same name with different scopes. Excel prevents you from defining a name that is not unique within its scope. Therefore, workbook scope makes for clearer range names and avoids confusion.Ī name must always be unique within its scope. If the scope was to a worksheet (say, Sheet1), then the range name would be “Sheet1!Cashflow” instead.
If you have defined a name, such as “Cashflow”, and its scope is the workbook, that name is recognised for all worksheets in that workbook (but not for any other workbook). To use this local name in another worksheet, you must qualify it by preceding it with the localised worksheet name: The scope of a name is the location within which the name is recognised without qualification.įor example, if you have defined a range name as “Profit” with its scope as Sheet1, rather than Workbook, then it will be recognised as “Profit” only in Sheet1 (ie, without qualification). All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level).
0 kommentar(er)
