Which code are you attempting to use from Debra's site?

Can you point us to the particular URL?

I don't see any code on the Dynamic Range pages.


Gord

On Sat, 10 Dec 2005 11:00:02 -0800, "cjtj4700"
<cjtj4700@discussions.microsoft.com> wrote:

>Hi Dave,
>I tried it that way as well. It must be my lack of understanding Macros.
>When I use Debras helpful web page, I follow alll the instruction to the
>letter and make sample sheets exactly like hers but it still doesn't work. I
>know nothing about "macros" or "view codes". I think her tips are based on
>the assumption that users are not using XL for the first time. Am I suppose
>to copy and paste the sample codes into my workbook?? When I did that I get a
>"run time error" message on my sheet with the DV and the code highlights:
>Set rng = ws.Range(Cells(1, Target.Column) & "List")
>"List" refers to "Target" on this line in the code:
>Private Sub Worksheet_Change(ByVal Target As Range)
>
>So it appears I need to make the code "specific" to what I am trying to do,
>I just don't know how.
>
>"Dave Peterson" wrote:
>
>> 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"
>> > > <cjtj4700@discussions.microsoft.com> 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
>>