Define a name local to a worksheet

[Rich Davies]

I have a workbook with many sheets with identical format and similar data. I
need to have cells and ranges named, but the names need to be local to a
particular worksheet.

I've accomplished that in the past by setting up one worksheet, naming
everything to my liking, then making a copy of the worksheet. In the copy,
all the names are nicely qualified with the name of the sheet.

I've done that, and now I need to expand the worksheets. I can't find a way
to add new names and make them local to any sheet. I don't want to go through
the copy process again due to the the impact on other sheets that collect
data from all these other sheet.

 

[Dave Peterson]

When you define the name--either via the Insert|Name|define dialog or by typing
the name into the namebox (to the left of the formulabar, you can include the
sheetname.

'Sheet 99'!myNameHere
(in the Namebox, with the range selected--and make sure you hit enter)

Or
Names in workbook: 'Sheet 99'!myNameHere
Refers to: ='Sheet 99'!$A$2:$Z$99
(in the dialog)




If you have to localize (or globalize) and existing name, you'll want to use Jan
Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

In fact, if you're working with names, you'll want this!

 

[Rich Davies]

Thanks. That works for me in the Insert|Name|Define dialog, but not in the
namebox. The dialog is much more tedious, but I guess I'll have to use what
works.

I'm using Excel 2004 on a MacBook. Maybe there's a problem since I really
don't have an enter key. Maybe it just doesn't work. Don't know.

 

[Dave Peterson]

It works ok in WinTel land. I would bet that it would work with Mac's, too (but
I don't use them).

Try it again and if it fails, explain what you did in detail.

If you don't get a good answer in this forum, I'd try:
news://msnews.microsoft.com/microsoft.public.mac.office.excel