defined names problem

[scott]

For a particular Name defined in a workbook 'June'
the range keeps changing periodically to some arbitrary range which
seems to never to be the same.

Her is how it is used

A calendar for scheduling.
Every 30 rows is a header with (similar to this)


Agent..|Line..|Van....|Store..|28-May-06..|29-May-06..|30-May-06..|31-May-06..|1-Jun-06..|2-Jun-06..|3-Jun-06

There are names defined for groups of weeks, 4 or 5 depending on the
month.

The reason is so that a whole month can be copied into other reports
easily in macros.

we have a couple versions of Excel in the office so one in particular
may be causing this

Anyone have a suggestion for figuring out what is changing the name
definition?
Thanks You

scott

 

[Tom Ogilvy]

Are you defining the name with absolute references?

Insert=>Name=>Define

Name: "June"
Refersto: =Sheet1!$F$2:$Z$12

 

[scott]

Thanks for asking. Yes

='2006 Schedule'!$A$655:$Q$778

 

[Tom Ogilvy]

Never heard of a problem such as this.

Could be that one computer has an addin that is causing the problem or is
running a macro that could be causing it.

Look for what is consistent.

Is it always the same name or subset of names as an example.

You could put in code to write to a common file each time the workbook is
saved. This would include who is using the file and the definition of the
range names that are problematic (or record it on a hidden sheet before the
save).

 

[Guest]

Hi Scott,

Defined Names can be a nightmare if they aren't managed properly. For
example, another user may be copying a sheet from another workbook that also
uses the same defined name ("June"). I find it safer to use local scope for
this because the names follow the worksheet. Unfortunately, so do global
names, and this can cause your previous defined name to change its .RefersTo
property depending on how the user answered the alert message during the copy
process. I didn't see a local scope in your description here but you might
want to modify things as follows:

Name: 'SheetName'!DefinedName
RefersTo: 'SheetName'!$Col$Row

To purge the global scope name, add a new sheet, then INSERT>NAMES>DEFINE,
then delete the global name from the list. Since this sheet is new it won't
have any local names listed, so all names you see in the list will be global.
Delete the sheet when you're done and save the file.

Hope this helps! GS

 

[scott]

Thank You

This may be something to investigate. I do have macros in other
workbooks copying these named ranges. I will have to go back through
them to see if this is maybe the case. Maybe I need to even rethink
using DefineNames this way.
I do appreciate the point in a helpful direction

Scott