 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2007Apply 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 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. 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 ! > [quoted text clipped - 22 lines] > hth, > hector. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.