Apply Names in Formula

[Dave Unger]

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

 

[Héctor Miguel]

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.

 

[Dave Unger]

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