help! named ranges/data validation changing by itself?

[Keith R]

I have a worksheet that contains the lists I want to use in various data
validation cells on other sheets

I created a named range for each list, using Steve Bullen's autoexpanding
ranges in case the user wants to add/delete items

I took out the fixed referencess ("$") in each named range, because it is
possible that the end user will add rows or columns in my list source
sheet, and I wanted to make sure the workbook was flexible enough to handle
it.

The problem is that the workbook seems to be changing the source ranges,
without columns being added or deleted in that source sheet!

For example, I type in named range "Team1Members" as:
=OFFSET(Sources!B4,0,0,COUNTA(Sources!B4:B50),1)

but I go back into the sheet later (having changed nothing in the sheet at
all), and find that it has reset itself to:

=OFFSET(Sources!C4,0,0,COUNTA(Sources!C4:C50),1)

Using XL97

Any ideas what might cause this kind of behavior?

TIA,
Keith

 

[Paul]

Without the absolute references, the definition of the named range will vary
depending on which cell you are in! (You can check this for yourself.
Select, say, A1 and look at the definition in Insert > Name > Define. Now
select B1 and look at the definition again.) I strongly advise you not to do
this - it's VERY confusing!!!

Anyway, the reason you give for having done it is invalid. Absolute
references adjust as you add/delete rows/columns. It's when you copy/paste
formulas that they don't adjust.