Losing Named Ranges

[uthooker]

I manage a workbook that runs about 3.5 MB that has several worksheets
and several named ranges. I use the named ranges mainly in VLOOKUP
formulas (e.g. =VLOOKUP(A1, NamedRange, 1, FALSE)).

A few of these named ranges refer to entire worksheets, and I'm finding
that periodically those ranges (that refer to entire worksheets) are
disappearing, causing the VLOOKUP formula to generate a #NAME error.
This happens most frequently to other associates that receive the
latest version of the workbook via email, but I can't seem to find a
pattern, nor can I determine the reason this is occuring.

Any ideas?

 

[JulieD]

Hi

just wondering why you're using range names to refer to entire worksheets
.... are you actually using all 256 columns and 65536 rows or is there
another reason? an answer to this question may allow for a solution that
avoids the use of entire worksheet range names.

 

[uthooker]

Hey Julie - Good question. The amount of data in the referenced
worksheets can vary each time I update the workbook. While not all
columns and rows are being used, referring to the entire worksheet is
an easy way to account for all the data.

Do you think the ranges wouldn't disappear if they were smaller? While
I won't argue against any solution that works, that seems like that
change doesn't quite address the problem - know what I mean?

 

[Debra Dalgleish]

You could create dynamic ranges, as described here:

http://www.contextures.com/xlNames01.html

They will automatically expand as rows or columns are added.

 

[JulieD]

Hi

i've never come across the problem of losing range names and i use them
extensively, but i've never defined a whole worksheet as a range name so
that would be the first thing i would eliminate via the use of dynamic range
names as posted by Debra.