What happens if you start your list in A1 instead of A2?
And change this to start at A1:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
to
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

(Remember nothing else in that column and no gaps!)


cjtj4700 wrote:

Thx Gord. I must be challenged. I opened a blank sheet and followed the
instruction link you included for dynamic ranges but no luck. Really green! I
made a list of A to G (A2:A7), named it "Letters", selected a cell and
entered the DV list "Letters". Went to Insert/Name/Define. Selected Letters
from my list of names and entered this into refers to
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
Go back to DV cell, list works, typed the letter H, doesn't get added to
source list.
Ugh!

"Gord Dibben" wrote:

See Debra Dalgleish's site for info on Dynamic Ranges

http://www.contextures.on.ca/xlNames01.html#Dynamic

And how to use them in DV lists.

Also check out her Home Excel page to see much more on Data Validation.

http://www.contextures.on.ca/tiptech.html


Gord Dibben Excel MVP

On Fri, 9 Dec 2005 15:02:02 -0800, "cjtj4700"
wrote:

Hi Excel Gurus,
I am very green @ XL so bear with me. I have created a shipping request
form and added data validation drop down lists for every category (ship to
company name, address, city, etc.) This data was imported from our preferred
carriers online address book. What I am looking for is a way to have the
source list update each time a user enters a new entry and have it placed
into the appropriate list alphabetically. These are routinely shipped to
locations but there are over 100 and growing each day.
Thus far XL appears to be able to do anything except make me a cup of
coffee so I just know what I am asking help for is probably "XL 101" to you
all! Please bear in mind that simply replying with a code will do a fly by on
me!!
Thx in advance. CJ


--

Dave Peterson