update data validation list with new entries??

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

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

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
>

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

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
>

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
>>

http://www.contextures.on.ca/DataValListAddSort.zi
I have approx. 10 dynamic ranges I have set up on a seperate sheet named
"Lists". No gaps. They all start from A:1, B:1, etc vertical. Debra types in
most of her instruction panes pertaining to saving newly entered data in DV
ranges:
"To view the event code that does this, right-click on the Data Validation
sheet tab, and choose View Code."

I copy & pasted each Debra "list" & "DV" code to my corresponding sheet.
Gotta be where I am mucking up! You'll need to "dumb" yourself down to my
level to get it probably. I've been using XL for about 2 months now. Each
time I "finish" a workbook I realize I can do so much more to it. It never
ends!

Thx Gord. Will be happy to show you anything else. Heck I'd email you the
darn thing if I could.

"Gord Dibben" wrote:

> 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
> >>
>

Let's start with named ranges first.

You have a worksheet with these lists in each column.

In Debra's example:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
the sheet is named Sheet1

Is that what your sheet is named?

If it's named something else, you'll have to adjust that formula.

So let's do one:
Select your worksheet
Insert|name|Define
Names in workbook box: DV_ColA
(or whatever you want)
Refers to box:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
If the sheet is named "Something Else":
=OFFSET('something else'!$A$1,0,0,COUNTA('something else'!$A:$A),1)

Click the ok button.

Now put some stuff in column A (starting at A1 and no gaps under it)
(Say A1:A5)

Now edit|goto
type: DV_ColA
In the Reference box.
Hit enter

Did your data (in A1:A5) get selected?

If it didn't, then there's a mistake somewhere. You'll have to try again.



cjtj4700 wrote:
>
> http://www.contextures.on.ca/DataValListAddSort.zi
> I have approx. 10 dynamic ranges I have set up on a seperate sheet named
> "Lists". No gaps. They all start from A:1, B:1, etc vertical. Debra types in
> most of her instruction panes pertaining to saving newly entered data in DV
> ranges:
> "To view the event code that does this, right-click on the Data Validation
> sheet tab, and choose View Code."
>
> I copy & pasted each Debra "list" & "DV" code to my corresponding sheet.
> Gotta be where I am mucking up! You'll need to "dumb" yourself down to my
> level to get it probably. I've been using XL for about 2 months now. Each
> time I "finish" a workbook I realize I can do so much more to it. It never
> ends!
>
> Thx Gord. Will be happy to show you anything else. Heck I'd email you the
> darn thing if I could.
>
> "Gord Dibben" wrote:
>
> > 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
> > >>
> >

--

Dave Peterson

OK

You have too many things going at once.

First you have to resolve the "dynamic range" issue then move on to the second
part, which Debra's download addresses.

That URL(plus a "p" after .zi leads to a download of the *.ZIP file.

UnZip that to your Excel data folder.

Open that file and right-click on the "data validation" sheet tab and "View
Code".

You will open a module in which the code lies.

The code is written for one and only one dynamic range which is the range
"NameList" found on "List" sheet and refers to

=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

To do the same with 10 different dynamic ranges on your worksheet you would
have to designate which list you are adding to.

This would involve some sort of 10 cases in a "Select Case" statement in the
code to make the particular DV list active.

I wouldn't bother with all that. I think you may be better off just having
one DV list for Company name and a series of VLOOKUP formulas to return the
address, city etc. in the cells you want tham.

Have your table of companies and their addresses, cities etc. on a separate
sheet in 10 columns.

Add new companies and their data to this list as you expand. Manually sort on
the company column after you add a new company and its 9 fields.

Make one DV list of just the the company names column and name it using the
Dynamic Range convention.

On your main sheet use that named list for a DV dropdown of companies only.

When you pick a name from that drop-down, your VLOOKUPs will fill in the other
cells where you need them.

If you want to send me your file via email, change the AT and DOT to @ and .


Gord Dibben Excel MVP


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

>http://www.contextures.on.ca/DataValListAddSort.zip
>I have approx. 10 dynamic ranges I have set up on a seperate sheet named
>"Lists". No gaps. They all start from A:1, B:1, etc vertical. Debra types in
>most of her instruction panes pertaining to saving newly entered data in DV
>ranges:
>"To view the event code that does this, right-click on the Data Validation
>sheet tab, and choose View Code."
>
>I copy & pasted each Debra "list" & "DV" code to my corresponding sheet.
>Gotta be where I am mucking up! You'll need to "dumb" yourself down to my
>level to get it probably. I've been using XL for about 2 months now. Each
>time I "finish" a workbook I realize I can do so much more to it. It never
>ends!
>
>Thx Gord. Will be happy to show you anything else. Heck I'd email you the
>darn thing if I could.
>
>"Gord Dibben" wrote:
>
>> 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
>> >>
>>

On it's way good sir!

Kindest regards. Talk to you soon.

Chris

"Gord Dibben" wrote:

> OK
>
> You have too many things going at once.
>
> First you have to resolve the "dynamic range" issue then move on to the second
> part, which Debra's download addresses.
>
> That URL(plus a "p" after .zi leads to a download of the *.ZIP file.
>
> UnZip that to your Excel data folder.
>
> Open that file and right-click on the "data validation" sheet tab and "View
> Code".
>
> You will open a module in which the code lies.
>
> The code is written for one and only one dynamic range which is the range
> "NameList" found on "List" sheet and refers to
>
> =OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)
>
> To do the same with 10 different dynamic ranges on your worksheet you would
> have to designate which list you are adding to.
>
> This would involve some sort of 10 cases in a "Select Case" statement in the
> code to make the particular DV list active.
>
> I wouldn't bother with all that. I think you may be better off just having
> one DV list for Company name and a series of VLOOKUP formulas to return the
> address, city etc. in the cells you want tham.
>
> Have your table of companies and their addresses, cities etc. on a separate
> sheet in 10 columns.
>
> Add new companies and their data to this list as you expand. Manually sort on
> the company column after you add a new company and its 9 fields.
>
> Make one DV list of just the the company names column and name it using the
> Dynamic Range convention.
>
> On your main sheet use that named list for a DV dropdown of companies only.
>
> When you pick a name from that drop-down, your VLOOKUPs will fill in the other
> cells where you need them.
>
> If you want to send me your file via email, change the AT and DOT to @ and .
>
>
> Gord Dibben Excel MVP
>
>
> On Sat, 10 Dec 2005 12:19:02 -0800, "cjtj4700"
> <cjtj4700@discussions.microsoft.com> wrote:
>
> >http://www.contextures.on.ca/DataValListAddSort.zip
> >I have approx. 10 dynamic ranges I have set up on a seperate sheet named
> >"Lists". No gaps. They all start from A:1, B:1, etc vertical. Debra types in
> >most of her instruction panes pertaining to saving newly entered data in DV
> >ranges:
> >"To view the event code that does this, right-click on the Data Validation
> >sheet tab, and choose View Code."
> >
> >I copy & pasted each Debra "list" & "DV" code to my corresponding sheet.
> >Gotta be where I am mucking up! You'll need to "dumb" yourself down to my
> >level to get it probably. I've been using XL for about 2 months now. Each
> >time I "finish" a workbook I realize I can do so much more to it. It never
> >ends!
> >
> >Thx Gord. Will be happy to show you anything else. Heck I'd email you the
> >darn thing if I could.
> >
> >"Gord Dibben" wrote:
> >
> >> 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
> >> >>
> >>
>

Hello, rather than using on sheet formulas and dropdowns why not use a
userform with listboxes and comboboxes?

You can view some of my demo stuff at
http://www.geocities.com/excelmarksway

you can send me the data at:
excelmarksway@yahoo.com.au

it takes about a few days to set up..

- -Mark



"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