Names in workbook

[Wesley & Paula Blank]

I have a workbook in which there are eventually twelve (months)
worksheets. When I copy a sheet for a new month, the name updates and
shows the new sheet name in the "define name" box to the right of the
name. The name reference remains the same even if I change the values
or locations of the named cell. For some reason, one of my names didn't
update and retained the old sheet location. If I update the name
manually, it changes on the previous sheet as well, making a mess in the
formulas of course (since the values and locations aren't the same).

How can I make the name unique to each sheet like the other names are?

Thanks

Wes

[Rob Bovey]

Hi Wesley,

There are two levels of defined names in Excel, Sheet-level and
Book-level. Sheet-level names are created by adding the name with the
SheetName!RangeName syntax. Book-level names are create by simply giving the
range a name without specifying any sheet.

The easiest way to see which range names are book-level is to insert a
new worksheet into your workbook and then choose Insert/Name/Define from the
menu while this new sheet is active. The only names that will appear in the
Define Names dialog are book-level.

It sounds to me like you want all your range names to be sheet-level
(which is a good practice in my opinion). In this case what you need to do
is take note of all the book-level names and what they refer to. Then remove
all the book level names and recreate them as sheet-level names on the
necessary sheets.

--
Rob Bovey, MCSE
The Payne Consulting Group
http://www.payneconsulting.com

Wesley & Paula Blank wrote in message <365580C3...@flash.net>...

[Wesley & Paula Blank]

Rob

Thanks a lot. I don't know how to assign sheet level names as opposed to book
level names. Will the sheet level names appear on a copied sheet (that's the
way I start a new month).

Wes

[Thomas Ogilvy]

Wes,
When you create the name, qualify it with the Sheet name.

Instead of enter the name
myName
Enter
Sheet1!myName
This will make a sheet level name. Once added, in the name box
(Insert=>Names=>Define) it will have the name on the left and the sheet name
on the right and only be visible when done from the sheet it is a sheet
level name of.

When the sheet is copied, the sheet name will be copied and the copied sheet
will have a similar defined name, but it will be relevant only to the copied
sheet (and only visible there).

From your description, it sounds like most of your names are sheet level
names and the one you are having a problem with is not a sheet level name.

HTH,
Tom Ogilvy

Wesley & Paula Blank wrote in message <3655C380...@flash.net>...

[Wesley & Paula Blank]

Tom

Thanks a lot. You cleared up my puzzlement as to why most of my names were ok
and remained sheet level even though I copied the sheet. The problem I can't
solve is that I have one workbook level (I guess) name on sheets 11 and 12. I
can't delete the name on both sheets and define a sheet level name on both
sheets. When I create a name on either sheet it appears on the other sheet with
references to the first sheet, which of course messes up my formulas with the
wrong sheets values. In other words I can't define individual sheet names for
some reason. I guess I haven't deleted the link between the sheets or
something.

I can't understand how I got into this mess in the first place. I thought I
followed the same procedure as before.

I would really appreciate your help on this.

Wes

[Ogilvy, Thomas, W., Mr., ODCSLOG]

Wes,
Try deleting the name from both sheets first before adding any names.
Or, if you can delete sheet 12, do that. Then work with just the name
on sheet 11 and when that is working, copy sheet 11 to become sheet 12.

I am not sure what is causing the problem - but names can be somewhat
persistent.

Regards,
Tom Ogilvy