Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Names in Formula
Hello,
I'm working on a workbook that has many formulas spread over 5 or 6 worksheets. In an attempt to tidy things up, I've been trying to convert a lot of the references to names, using insert | name | apply. This only seems to works for me if the formula and the reference are on the same sheet, else I get a message "Microsoft Excel cannot find any references to replace'. According to what I've read, this should work regardless if they're on the same sheet or not. Maybe someone can tell me what I'm doing wrong (using Excel 97) regards Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Names in Formula
hi, Dave !
I'm working on a workbook that has many formulas spread over 5 or 6 worksheets. ... to tidy things up, I've been trying toconvert a lot of the references to names, using insert | name | apply. This only seems to works for me if the formula and the reference are onthe same sheet else I get a message "Microsoft Excel cannot find anyreferences to replace'. According to what I've read, this should work regardless if they're on the same sheet or not. Maybe someone can tell me what I'm doing wrong (using Excel 97) AFAIK, names has two 'levels' of application/availability/usage/... workbook-level & worksheet-level when your workbook has two or more worksheets... the 'first time' you define a name, it assumes the workbook-level, and... - it can be 'called' from any worksheet in your workbook, but can NOT be re/used to refers to other worksheet's range - if you 'need' to create another same-name to refers to ranges in another worksheet, then... you will need to preceed the same-name with the -new- worksheet's name and the sign !< when you *first* define names in one worksheet... and you *copy* the worksheet for the same workbook... excel 'handles' future troubles by *creating* a NEW set of same-names, but referring to the -new- copied worksheet, and... - when you *call* a name, excel first looks if it exists in the 'calling' worksheet, otherwise, 'calls' a workbook-level name when you need to call a same-name but referring to 'other' worksheet range, you need to make a complete reference to 'the name' I hope the above-mentioned doesn't sounds like a tongue twister :) hth, hector. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Apply Names in Formula
Hi Héctor,
Thanks for your reply. As far as I can tell Excel 97 only contains workbook level names, so your explanation doesn't appear to apply here. I've asked other people about this, so far haven't had an explanation. If you're building your own spreadsheet from scratch you might not use this feature, but I'm working on a customer's spreadsheet that is sprawling to the extent that it's almost unmanageable, and applying names would be a big help in cleaning it up (if it worked like it should). So far it's still a mystery. regards Dave Héctor Miguel wrote: hi, Dave ! I'm working on a workbook that has many formulas spread over 5 or 6 worksheets. ... to tidy things up, I've been trying toconvert a lot of the references to names, using insert | name | apply. This only seems to works for me if the formula and the reference are onthe same sheet else I get a message "Microsoft Excel cannot find anyreferences to replace'. According to what I've read, this should work regardless if they're on the same sheet or not. Maybe someone can tell me what I'm doing wrong (using Excel 97) AFAIK, names has two 'levels' of application/availability/usage/... workbook-level & worksheet-level when your workbook has two or more worksheets... the 'first time' you define a name, it assumes the workbook-level, and... - it can be 'called' from any worksheet in your workbook, but can NOT be re/used to refers to other worksheet's range - if you 'need' to create another same-name to refers to ranges in another worksheet, then... you will need to preceed the same-name with the -new- worksheet's name and the sign !< when you *first* define names in one worksheet... and you *copy* the worksheet for the same workbook... excel 'handles' future troubles by *creating* a NEW set of same-names, but referring to the -new- copied worksheet, and... - when you *call* a name, excel first looks if it exists in the 'calling' worksheet, otherwise, 'calls' a workbook-level name when you need to call a same-name but referring to 'other' worksheet range, you need to make a complete reference to 'the name' I hope the above-mentioned doesn't sounds like a tongue twister :) hth, hector. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
apply all names | Excel Programming | |||
I am not able to apply names in already created Vlookup formula. | Excel Worksheet Functions | |||
How do you apply Names in formulas? | Excel Discussion (Misc queries) | |||
Selecting ALL names when using Insert/Names/Apply | Excel Worksheet Functions | |||
One More Try: Names/Apply on other worksheet | Excel Worksheet Functions |