Why Excel does automatically change the formula?

[Guest]

Hi,

When I am using a defined name, say EndDate, in formula it works fine (i.e.
: =EndDate). But when I precede the defined name by the name of the sheet
where the cells defining the name are located, Microsoft Excel replace
automatically the name of the sheet by the name of the workbook. For example
if the name “EndDate†refers to cells contained in the sheet named “Ref†in
the workbook named “MyBook.xlsâ€. If I enter the formula “=Ref!EndDate†in a
cell of another worksheet of the same workbook, Excel will automatically
replace the formula by: “=MyBook!EndDateâ€.

Thanks for any help.

 

[JE McGimpsey]

Names can be either workbook or worksheet level. By default, names
entered in the Name box (or the Insert/Name/Define input box) are
workbook level, so that they refer to the same range in the workbook
regardless of which sheet is active.

Worksheet-level names, OTOH, are defined by entering sheet1!name in the
name box (or Sheet1!name in the Insert/Name/Define box), and applies
only to that sheet.

So if you define a workbook level name, "myname" on Sheet1, cell A1,
then you can use "myname" anywhere in the workbook to refer to Sheet1!A1.

OTOH, if you, on Sheet1!A! define "Sheet1!myname" and on Sheet2!A5 also
define "Sheet2!myname", then when you enter "myname" in a formula on
sheet1, it refers to Sheet1!myname. In order to refer to sheet1!A1 in a
formula on on sheet2, you need to add the sheet reference, Sheet1!myname.

Once you define a workbook level name, you can't define the same name in
the sheets, which is why you're getting the response you did.