Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why Excel does automatically change the formula?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why Excel does automatically change the formula?
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. In article , Warda wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula help to change row colour automatically by some conditions | Excel Worksheet Functions | |||
excel macro to automatically change default printer | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Can you change something in Excel to automatically insert an equa. | Excel Discussion (Misc queries) | |||
How do I change the Cell color automatically in an Excel sheet wh. | Excel Worksheet Functions |