software

Hi~
I have been hooked up with a very nice VLookup tool for my companies
shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
would like to know if anyone out there would be willing to send me the code
or let me send them my workbook to allow new entries into any dropdown cell
to be auto added to its source list, alphabetically. Per Contextures.com,
there is a macro that enables this and VB does a fly by on me. The sample
spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.

Thanks as always!

First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
for me) quot;List1quot;.

I defined that name using this:
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
So that it would expand and contract based on the number of entries in column A
of Sheet2.

Debra Dalgleish has instructions:
www.contextures.on.ca/xlNames01.html#Dynamic

Then I applied data|validation to A1 of sheet1.
I chose List and used List1 as the range for the list.

But on the Error alert tab of the Data|Validation dialog, I chose:
Style: Warning
Title: New Entry!
Error Message: New entry will be added to list if you click ok.

Then I used a worksheet_change event that waited for a change to A1. Note that
this will not work in xl97. (But Debra shows away around it using a button near
the dropdown.)

This was the code behind sheet1:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myList As Range

If Target.Cells.Count gt; 1 Then Exit Sub
If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
If Target.Value = quot;quot; Then Exit Sub

Set myList = Nothing
Select Case LCase(Target.Address(0, 0))
Case Is = quot;a1quot;
Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
Case Is = quot;b9quot;
Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
Case Is = quot;c3quot;
Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
'etc
End Select

If myList Is Nothing Then
Exit Sub
End If

If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
'already there, do nothing
Else
With myList
.Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
Set myList = .Resize(.Rows.Count 1, 1)
End With

With myList
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
End If

End Sub

It seemed to work ok.

If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
www.cpearson.com/excel/events.htm

David McRitchie's site:
www.mvps.org/dmcritchie/excel/event.htmcjtj4700 wrote:
gt;
gt; Hi~
gt; I have been hooked up with a very nice VLookup tool for my companies
gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt; would like to know if anyone out there would be willing to send me the code
gt; or let me send them my workbook to allow new entries into any dropdown cell
gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt; there is a macro that enables this and VB does a fly by on me. The sample
gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt;
gt; Thanks as always!

--

Dave Peterson

Chris

Dave's code will work for the three DV dropdowns you have. Just change the
addresses.

Let me qualify the above...........Will work fine for quot;Carriersquot; and
quot;Servicesquot; which are of irregular length and sorted independently.

BUT.......being able to add to and sort the quot;Companyquot; column won't do you much
good without adding all the other pertinent data that goes with each new
Company you add.

i.e. Addressone, Addresstwo, City, State, Country, Postal/ZIP Code, Contact,
Phone No.

How do you propose to add those with each new Company name?

I would suggest you abandon the idea of adding through the DV dropdown for the
Company and its associated columns and use the standard Data Form for entering
new items across the board.

Select columns D:L and name it Database.

Go to Datagt;Data Form and your Database will be selected. Click on quot;Newquot; and
add all the pertinent information in the appropriate dialog boxes.

Close the Form then click on Name Box and quot;Databasequot;. Datagt;Sortgt;Sort on
quot;Companyquot; will sort all columns together in that range.GordOn Wed, 14 Dec 2005 10:01:23 -0600, Dave Peterson gt;
wrote:

gt;First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt;for me) quot;List1quot;.
gt;
gt;I defined that name using this:
gt;=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt;So that it would expand and contract based on the number of entries in column A
gt;of Sheet2.
gt;
gt;Debra Dalgleish has instructions:
gt;www.contextures.on.ca/xlNames01.html#Dynamic
gt;
gt;Then I applied data|validation to A1 of sheet1.
gt;I chose List and used List1 as the range for the list.
gt;
gt;But on the Error alert tab of the Data|Validation dialog, I chose:
gt;Style: Warning
gt;Title: New Entry!
gt;Error Message: New entry will be added to list if you click ok.
gt;
gt;Then I used a worksheet_change event that waited for a change to A1. Note that
gt;this will not work in xl97. (But Debra shows away around it using a button near
gt;the dropdown.)
gt;
gt;This was the code behind sheet1:
gt;
gt;Option Explicit
gt;Private Sub Worksheet_Change(ByVal Target As Range)
gt;
gt; Dim myList As Range
gt;
gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt; If Target.Value = quot;quot; Then Exit Sub
gt;
gt; Set myList = Nothing
gt; Select Case LCase(Target.Address(0, 0))
gt; Case Is = quot;a1quot;
gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt; Case Is = quot;b9quot;
gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt; Case Is = quot;c3quot;
gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt; 'etc
gt; End Select
gt;
gt; If myList Is Nothing Then
gt; Exit Sub
gt; End If
gt;
gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt; 'already there, do nothing
gt; Else
gt; With myList
gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt; Set myList = .Resize(.Rows.Count 1, 1)
gt; End With
gt;
gt; With myList
gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt; End With
gt; End If
gt;
gt;End Sub
gt;
gt;It seemed to work ok.
gt;
gt;If you're new to macros, you may want to read David McRitchie's intro at:
gt;www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt;If you want to read more about these kinds of events:
gt;
gt;Chip Pearson's site:
gt;www.cpearson.com/excel/events.htm
gt;
gt;David McRitchie's site:
gt;www.mvps.org/dmcritchie/excel/event.htm
gt;
gt;
gt;cjtj4700 wrote:
gt;gt;
gt;gt; Hi~
gt;gt; I have been hooked up with a very nice VLookup tool for my companies
gt;gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt;gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt;gt; would like to know if anyone out there would be willing to send me the code
gt;gt; or let me send them my workbook to allow new entries into any dropdown cell
gt;gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt;gt; there is a macro that enables this and VB does a fly by on me. The sample
gt;gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt;gt;
gt;gt; Thanks as always!

Thanks Dave. I am learning so much. If I sent you my actual named ranges,
sheet names and all pertinent locations of my spreadsheet could you plop them
into your code? Then I imagine I could paste it. Or I could email you my
spreadsheet?
I really appreciate your and everyones help.

quot;Dave Petersonquot; wrote:

gt; First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt; for me) quot;List1quot;.
gt;
gt; I defined that name using this:
gt; =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt; So that it would expand and contract based on the number of entries in column A
gt; of Sheet2.
gt;
gt; Debra Dalgleish has instructions:
gt; www.contextures.on.ca/xlNames01.html#Dynamic
gt;
gt; Then I applied data|validation to A1 of sheet1.
gt; I chose List and used List1 as the range for the list.
gt;
gt; But on the Error alert tab of the Data|Validation dialog, I chose:
gt; Style: Warning
gt; Title: New Entry!
gt; Error Message: New entry will be added to list if you click ok.
gt;
gt; Then I used a worksheet_change event that waited for a change to A1. Note that
gt; this will not work in xl97. (But Debra shows away around it using a button near
gt; the dropdown.)
gt;
gt; This was the code behind sheet1:
gt;
gt; Option Explicit
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt;
gt; Dim myList As Range
gt;
gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt; If Target.Value = quot;quot; Then Exit Sub
gt;
gt; Set myList = Nothing
gt; Select Case LCase(Target.Address(0, 0))
gt; Case Is = quot;a1quot;
gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt; Case Is = quot;b9quot;
gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt; Case Is = quot;c3quot;
gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt; 'etc
gt; End Select
gt;
gt; If myList Is Nothing Then
gt; Exit Sub
gt; End If
gt;
gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt; 'already there, do nothing
gt; Else
gt; With myList
gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt; Set myList = .Resize(.Rows.Count 1, 1)
gt; End With
gt;
gt; With myList
gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt; End With
gt; End If
gt;
gt; End Sub
gt;
gt; It seemed to work ok.
gt;
gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt; If you want to read more about these kinds of events:
gt;
gt; Chip Pearson's site:
gt; www.cpearson.com/excel/events.htm
gt;
gt; David McRitchie's site:
gt; www.mvps.org/dmcritchie/excel/event.htm
gt;
gt;
gt; cjtj4700 wrote:
gt; gt;
gt; gt; Hi~
gt; gt; I have been hooked up with a very nice VLookup tool for my companies
gt; gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt; gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt; gt; would like to know if anyone out there would be willing to send me the code
gt; gt; or let me send them my workbook to allow new entries into any dropdown cell
gt; gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt; gt; there is a macro that enables this and VB does a fly by on me. The sample
gt; gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt; gt;
gt; gt; Thanks as always!
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Hi Gord~
Thx. I will plug away at this and see what happens Regarding new entries,
users not finding their quot;Companyquot;, must input all of that new pertinent info
you apeak of. I was under the impression that when doing this a new completed
company entry would get added to the VLoookup table??

Bear with my greeness again, Dave or Gord, do I just copy and paste that
code? Words like quot;rangequot;, quot;targetquot; and quot;addressquot; spook me. It goes on the
assumption that the requestor knows what he is doing! LOL

I just want to be done with this crazy thing. Everyday, I learn something
new from you guys and it keeps going and going......

quot;Gord Dibbenquot; wrote:

gt; Chris
gt;
gt; Dave's code will work for the three DV dropdowns you have. Just change the
gt; addresses.
gt;
gt; Let me qualify the above...........Will work fine for quot;Carriersquot; and
gt; quot;Servicesquot; which are of irregular length and sorted independently.
gt;
gt; BUT.......being able to add to and sort the quot;Companyquot; column won't do you much
gt; good without adding all the other pertinent data that goes with each new
gt; Company you add.
gt;
gt; i.e. Addressone, Addresstwo, City, State, Country, Postal/ZIP Code, Contact,
gt; Phone No.
gt;
gt; How do you propose to add those with each new Company name?
gt;
gt; I would suggest you abandon the idea of adding through the DV dropdown for the
gt; Company and its associated columns and use the standard Data Form for entering
gt; new items across the board.
gt;
gt; Select columns D:L and name it Database.
gt;
gt; Go to Datagt;Data Form and your Database will be selected. Click on quot;Newquot; and
gt; add all the pertinent information in the appropriate dialog boxes.
gt;
gt; Close the Form then click on Name Box and quot;Databasequot;. Datagt;Sortgt;Sort on
gt; quot;Companyquot; will sort all columns together in that range.
gt;
gt;
gt; Gord
gt;
gt;
gt; On Wed, 14 Dec 2005 10:01:23 -0600, Dave Peterson gt;
gt; wrote:
gt;
gt; gt;First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt; gt;for me) quot;List1quot;.
gt; gt;
gt; gt;I defined that name using this:
gt; gt;=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt; gt;So that it would expand and contract based on the number of entries in column A
gt; gt;of Sheet2.
gt; gt;
gt; gt;Debra Dalgleish has instructions:
gt; gt;www.contextures.on.ca/xlNames01.html#Dynamic
gt; gt;
gt; gt;Then I applied data|validation to A1 of sheet1.
gt; gt;I chose List and used List1 as the range for the list.
gt; gt;
gt; gt;But on the Error alert tab of the Data|Validation dialog, I chose:
gt; gt;Style: Warning
gt; gt;Title: New Entry!
gt; gt;Error Message: New entry will be added to list if you click ok.
gt; gt;
gt; gt;Then I used a worksheet_change event that waited for a change to A1. Note that
gt; gt;this will not work in xl97. (But Debra shows away around it using a button near
gt; gt;the dropdown.)
gt; gt;
gt; gt;This was the code behind sheet1:
gt; gt;
gt; gt;Option Explicit
gt; gt;Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt;
gt; gt; Dim myList As Range
gt; gt;
gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt; gt; If Target.Value = quot;quot; Then Exit Sub
gt; gt;
gt; gt; Set myList = Nothing
gt; gt; Select Case LCase(Target.Address(0, 0))
gt; gt; Case Is = quot;a1quot;
gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt; gt; Case Is = quot;b9quot;
gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt; gt; Case Is = quot;c3quot;
gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt; gt; 'etc
gt; gt; End Select
gt; gt;
gt; gt; If myList Is Nothing Then
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt; gt; 'already there, do nothing
gt; gt; Else
gt; gt; With myList
gt; gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt; gt; Set myList = .Resize(.Rows.Count 1, 1)
gt; gt; End With
gt; gt;
gt; gt; With myList
gt; gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt;End Sub
gt; gt;
gt; gt;It seemed to work ok.
gt; gt;
gt; gt;If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt;www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;
gt; gt;If you want to read more about these kinds of events:
gt; gt;
gt; gt;Chip Pearson's site:
gt; gt;www.cpearson.com/excel/events.htm
gt; gt;
gt; gt;David McRitchie's site:
gt; gt;www.mvps.org/dmcritchie/excel/event.htm
gt; gt;
gt; gt;
gt; gt;cjtj4700 wrote:
gt; gt;gt;
gt; gt;gt; Hi~
gt; gt;gt; I have been hooked up with a very nice VLookup tool for my companies
gt; gt;gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt; gt;gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt; gt;gt; would like to know if anyone out there would be willing to send me the code
gt; gt;gt; or let me send them my workbook to allow new entries into any dropdown cell
gt; gt;gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt; gt;gt; there is a macro that enables this and VB does a fly by on me. The sample
gt; gt;gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt; gt;gt;
gt; gt;gt; Thanks as always!
gt;

I think you should try it yourself. If you're ever going to support changes to
the workbook, you're gonna want to do it yourself.

In my code, A1 used a list from Sheet2 named List1
B9 used List2
C3 used List3

The code itself goes behind the worksheet with the data|validation cells.

Just rightclick on that worksheet's tab and choose View code. You'll see a code
window open and you'll just paste the code there.
cjtj4700 wrote:
gt;
gt; Thanks Dave. I am learning so much. If I sent you my actual named ranges,
gt; sheet names and all pertinent locations of my spreadsheet could you plop them
gt; into your code? Then I imagine I could paste it. Or I could email you my
gt; spreadsheet?
gt; I really appreciate your and everyones help.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt; gt; for me) quot;List1quot;.
gt; gt;
gt; gt; I defined that name using this:
gt; gt; =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt; gt; So that it would expand and contract based on the number of entries in column A
gt; gt; of Sheet2.
gt; gt;
gt; gt; Debra Dalgleish has instructions:
gt; gt; www.contextures.on.ca/xlNames01.html#Dynamic
gt; gt;
gt; gt; Then I applied data|validation to A1 of sheet1.
gt; gt; I chose List and used List1 as the range for the list.
gt; gt;
gt; gt; But on the Error alert tab of the Data|Validation dialog, I chose:
gt; gt; Style: Warning
gt; gt; Title: New Entry!
gt; gt; Error Message: New entry will be added to list if you click ok.
gt; gt;
gt; gt; Then I used a worksheet_change event that waited for a change to A1. Note that
gt; gt; this will not work in xl97. (But Debra shows away around it using a button near
gt; gt; the dropdown.)
gt; gt;
gt; gt; This was the code behind sheet1:
gt; gt;
gt; gt; Option Explicit
gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt;
gt; gt; Dim myList As Range
gt; gt;
gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt; gt; If Target.Value = quot;quot; Then Exit Sub
gt; gt;
gt; gt; Set myList = Nothing
gt; gt; Select Case LCase(Target.Address(0, 0))
gt; gt; Case Is = quot;a1quot;
gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt; gt; Case Is = quot;b9quot;
gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt; gt; Case Is = quot;c3quot;
gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt; gt; 'etc
gt; gt; End Select
gt; gt;
gt; gt; If myList Is Nothing Then
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt; gt; 'already there, do nothing
gt; gt; Else
gt; gt; With myList
gt; gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt; gt; Set myList = .Resize(.Rows.Count 1, 1)
gt; gt; End With
gt; gt;
gt; gt; With myList
gt; gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt; gt; End With
gt; gt; End If
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; It seemed to work ok.
gt; gt;
gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;
gt; gt; If you want to read more about these kinds of events:
gt; gt;
gt; gt; Chip Pearson's site:
gt; gt; www.cpearson.com/excel/events.htm
gt; gt;
gt; gt; David McRitchie's site:
gt; gt; www.mvps.org/dmcritchie/excel/event.htm
gt; gt;
gt; gt;
gt; gt; cjtj4700 wrote:
gt; gt; gt;
gt; gt; gt; Hi~
gt; gt; gt; I have been hooked up with a very nice VLookup tool for my companies
gt; gt; gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt; gt; gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt; gt; gt; would like to know if anyone out there would be willing to send me the code
gt; gt; gt; or let me send them my workbook to allow new entries into any dropdown cell
gt; gt; gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt; gt; gt; there is a macro that enables this and VB does a fly by on me. The sample
gt; gt; gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt; gt; gt;
gt; gt; gt; Thanks as always!
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

If user does not find their Company name and chooses to enter/add it in the DV
dropdown, that's all that will be added ro the VLOOKUP table....just the name.

What about all the other details that have to go with that name?

How/where do you propose to enter that?

You would need about 10 DV dropdowns and 10 Select Case statements in the code
plus 10 distinct dynamic ranges.

PLUS...........You don't want the data in D:L to be sorted independently. You
want it sorted together. That's how a VLOOKUP table operates. If each column
is sorted independently, the table will not stay together and be useless.

The DV dropdowns are for filling in the cells that feed the VLOOKUP formulas,
not for adding data

That's why I recommend using the Data Form.

If user doesn't find their Company in the DV dropdown, open the data form and
fill it in under quot;Newquot;. You can record a macro for opening the Form and
assign to a button for users to click.

The newest data will fill in below the last entry in the VLOOKUP table.

You can then sort that Database by Company column.GordOn Wed, 14 Dec 2005 14:01:41 -0800, quot;cjtj4700quot;
gt; wrote:

gt;Hi Gord~
gt;Thx. I will plug away at this and see what happens Regarding new entries,
gt;users not finding their quot;Companyquot;, must input all of that new pertinent info
gt;you apeak of. I was under the impression that when doing this a new completed
gt;company entry would get added to the VLoookup table??
gt;
gt;Bear with my greeness again, Dave or Gord, do I just copy and paste that
gt;code? Words like quot;rangequot;, quot;targetquot; and quot;addressquot; spook me. It goes on the
gt;assumption that the requestor knows what he is doing! LOL
gt;
gt;I just want to be done with this crazy thing. Everyday, I learn something
gt;new from you guys and it keeps going and going......
gt;
gt;quot;Gord Dibbenquot; wrote:
gt;
gt;gt; Chris
gt;gt;
gt;gt; Dave's code will work for the three DV dropdowns you have. Just change the
gt;gt; addresses.
gt;gt;
gt;gt; Let me qualify the above...........Will work fine for quot;Carriersquot; and
gt;gt; quot;Servicesquot; which are of irregular length and sorted independently.
gt;gt;
gt;gt; BUT.......being able to add to and sort the quot;Companyquot; column won't do you much
gt;gt; good without adding all the other pertinent data that goes with each new
gt;gt; Company you add.
gt;gt;
gt;gt; i.e. Addressone, Addresstwo, City, State, Country, Postal/ZIP Code, Contact,
gt;gt; Phone No.
gt;gt;
gt;gt; How do you propose to add those with each new Company name?
gt;gt;
gt;gt; I would suggest you abandon the idea of adding through the DV dropdown for the
gt;gt; Company and its associated columns and use the standard Data Form for entering
gt;gt; new items across the board.
gt;gt;
gt;gt; Select columns D:L and name it Database.
gt;gt;
gt;gt; Go to Datagt;Data Form and your Database will be selected. Click on quot;Newquot; and
gt;gt; add all the pertinent information in the appropriate dialog boxes.
gt;gt;
gt;gt; Close the Form then click on Name Box and quot;Databasequot;. Datagt;Sortgt;Sort on
gt;gt; quot;Companyquot; will sort all columns together in that range.
gt;gt;
gt;gt;
gt;gt; Gord
gt;gt;
gt;gt;
gt;gt; On Wed, 14 Dec 2005 10:01:23 -0600, Dave Peterson gt;
gt;gt; wrote:
gt;gt;
gt;gt; gt;First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt;gt; gt;for me) quot;List1quot;.
gt;gt; gt;
gt;gt; gt;I defined that name using this:
gt;gt; gt;=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt;gt; gt;So that it would expand and contract based on the number of entries in column A
gt;gt; gt;of Sheet2.
gt;gt; gt;
gt;gt; gt;Debra Dalgleish has instructions:
gt;gt; gt;www.contextures.on.ca/xlNames01.html#Dynamic
gt;gt; gt;
gt;gt; gt;Then I applied data|validation to A1 of sheet1.
gt;gt; gt;I chose List and used List1 as the range for the list.
gt;gt; gt;
gt;gt; gt;But on the Error alert tab of the Data|Validation dialog, I chose:
gt;gt; gt;Style: Warning
gt;gt; gt;Title: New Entry!
gt;gt; gt;Error Message: New entry will be added to list if you click ok.
gt;gt; gt;
gt;gt; gt;Then I used a worksheet_change event that waited for a change to A1. Note that
gt;gt; gt;this will not work in xl97. (But Debra shows away around it using a button near
gt;gt; gt;the dropdown.)
gt;gt; gt;
gt;gt; gt;This was the code behind sheet1:
gt;gt; gt;
gt;gt; gt;Option Explicit
gt;gt; gt;Private Sub Worksheet_Change(ByVal Target As Range)
gt;gt; gt;
gt;gt; gt; Dim myList As Range
gt;gt; gt;
gt;gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt;gt; gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt;gt; gt; If Target.Value = quot;quot; Then Exit Sub
gt;gt; gt;
gt;gt; gt; Set myList = Nothing
gt;gt; gt; Select Case LCase(Target.Address(0, 0))
gt;gt; gt; Case Is = quot;a1quot;
gt;gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt;gt; gt; Case Is = quot;b9quot;
gt;gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt;gt; gt; Case Is = quot;c3quot;
gt;gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt;gt; gt; 'etc
gt;gt; gt; End Select
gt;gt; gt;
gt;gt; gt; If myList Is Nothing Then
gt;gt; gt; Exit Sub
gt;gt; gt; End If
gt;gt; gt;
gt;gt; gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt;gt; gt; 'already there, do nothing
gt;gt; gt; Else
gt;gt; gt; With myList
gt;gt; gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt;gt; gt; Set myList = .Resize(.Rows.Count 1, 1)
gt;gt; gt; End With
gt;gt; gt;
gt;gt; gt; With myList
gt;gt; gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt;gt; gt; End With
gt;gt; gt; End If
gt;gt; gt;
gt;gt; gt;End Sub
gt;gt; gt;
gt;gt; gt;It seemed to work ok.
gt;gt; gt;
gt;gt; gt;If you're new to macros, you may want to read David McRitchie's intro at:
gt;gt; gt;www.mvps.org/dmcritchie/excel/getstarted.htm
gt;gt; gt;
gt;gt; gt;If you want to read more about these kinds of events:
gt;gt; gt;
gt;gt; gt;Chip Pearson's site:
gt;gt; gt;www.cpearson.com/excel/events.htm
gt;gt; gt;
gt;gt; gt;David McRitchie's site:
gt;gt; gt;www.mvps.org/dmcritchie/excel/event.htm
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;cjtj4700 wrote:
gt;gt; gt;gt;
gt;gt; gt;gt; Hi~
gt;gt; gt;gt; I have been hooked up with a very nice VLookup tool for my companies
gt;gt; gt;gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt;gt; gt;gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt;gt; gt;gt; would like to know if anyone out there would be willing to send me the code
gt;gt; gt;gt; or let me send them my workbook to allow new entries into any dropdown cell
gt;gt; gt;gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt;gt; gt;gt; there is a macro that enables this and VB does a fly by on me. The sample
gt;gt; gt;gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt;gt; gt;gt;
gt;gt; gt;gt; Thanks as always!
gt;gt;

Chris

Or just do away with the VLOOKUPS completely and have your users fill in from
10 or 12 DV dropdowns with the ability to add to and sort each of these.

Quite unwieldy in my estimation.

If you look at Dave's code he has provided for 3 cases using 3 dynamic ranges.
You would have to provide for 10-12 cases and chase your users around from
dropdown to dropdown when they wanted to add new information in all 10-12
columns.Gord

On Wed, 14 Dec 2005 15:02:51 -0800, Gord Dibben lt;gorddibbATshawDOTcagt; wrote:

gt;If user does not find their Company name and chooses to enter/add it in the DV
gt;dropdown, that's all that will be added ro the VLOOKUP table....just the name.
gt;
gt;What about all the other details that have to go with that name?
gt;
gt;How/where do you propose to enter that?
gt;
gt;You would need about 10 DV dropdowns and 10 Select Case statements in the code
gt;plus 10 distinct dynamic ranges.
gt;
gt;PLUS...........You don't want the data in D:L to be sorted independently. You
gt;want it sorted together. That's how a VLOOKUP table operates. If each column
gt;is sorted independently, the table will not stay together and be useless.
gt;
gt;The DV dropdowns are for filling in the cells that feed the VLOOKUP formulas,
gt;not for adding data
gt;
gt;That's why I recommend using the Data Form.
gt;
gt;If user doesn't find their Company in the DV dropdown, open the data form and
gt;fill it in under quot;Newquot;. You can record a macro for opening the Form and
gt;assign to a button for users to click.
gt;
gt;The newest data will fill in below the last entry in the VLOOKUP table.
gt;
gt;You can then sort that Database by Company column.
gt;
gt;
gt;Gord
gt;
gt;
gt;On Wed, 14 Dec 2005 14:01:41 -0800, quot;cjtj4700quot;
gt; wrote:
gt;
gt;gt;Hi Gord~
gt;gt;Thx. I will plug away at this and see what happens Regarding new entries,
gt;gt;users not finding their quot;Companyquot;, must input all of that new pertinent info
gt;gt;you apeak of. I was under the impression that when doing this a new completed
gt;gt;company entry would get added to the VLoookup table??
gt;gt;
gt;gt;Bear with my greeness again, Dave or Gord, do I just copy and paste that
gt;gt;code? Words like quot;rangequot;, quot;targetquot; and quot;addressquot; spook me. It goes on the
gt;gt;assumption that the requestor knows what he is doing! LOL
gt;gt;
gt;gt;I just want to be done with this crazy thing. Everyday, I learn something
gt;gt;new from you guys and it keeps going and going......
gt;gt;
gt;gt;quot;Gord Dibbenquot; wrote:
gt;gt;
gt;gt;gt; Chris
gt;gt;gt;
gt;gt;gt; Dave's code will work for the three DV dropdowns you have. Just change the
gt;gt;gt; addresses.
gt;gt;gt;
gt;gt;gt; Let me qualify the above...........Will work fine for quot;Carriersquot; and
gt;gt;gt; quot;Servicesquot; which are of irregular length and sorted independently.
gt;gt;gt;
gt;gt;gt; BUT.......being able to add to and sort the quot;Companyquot; column won't do you much
gt;gt;gt; good without adding all the other pertinent data that goes with each new
gt;gt;gt; Company you add.
gt;gt;gt;
gt;gt;gt; i.e. Addressone, Addresstwo, City, State, Country, Postal/ZIP Code, Contact,
gt;gt;gt; Phone No.
gt;gt;gt;
gt;gt;gt; How do you propose to add those with each new Company name?
gt;gt;gt;
gt;gt;gt; I would suggest you abandon the idea of adding through the DV dropdown for the
gt;gt;gt; Company and its associated columns and use the standard Data Form for entering
gt;gt;gt; new items across the board.
gt;gt;gt;
gt;gt;gt; Select columns D:L and name it Database.
gt;gt;gt;
gt;gt;gt; Go to Datagt;Data Form and your Database will be selected. Click on quot;Newquot; and
gt;gt;gt; add all the pertinent information in the appropriate dialog boxes.
gt;gt;gt;
gt;gt;gt; Close the Form then click on Name Box and quot;Databasequot;. Datagt;Sortgt;Sort on
gt;gt;gt; quot;Companyquot; will sort all columns together in that range.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; Gord
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; On Wed, 14 Dec 2005 10:01:23 -0600, Dave Peterson gt;
gt;gt;gt; wrote:
gt;gt;gt;
gt;gt;gt; gt;First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt;gt;gt; gt;for me) quot;List1quot;.
gt;gt;gt; gt;
gt;gt;gt; gt;I defined that name using this:
gt;gt;gt; gt;=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt;gt;gt; gt;So that it would expand and contract based on the number of entries in column A
gt;gt;gt; gt;of Sheet2.
gt;gt;gt; gt;
gt;gt;gt; gt;Debra Dalgleish has instructions:
gt;gt;gt; gt;www.contextures.on.ca/xlNames01.html#Dynamic
gt;gt;gt; gt;
gt;gt;gt; gt;Then I applied data|validation to A1 of sheet1.
gt;gt;gt; gt;I chose List and used List1 as the range for the list.
gt;gt;gt; gt;
gt;gt;gt; gt;But on the Error alert tab of the Data|Validation dialog, I chose:
gt;gt;gt; gt;Style: Warning
gt;gt;gt; gt;Title: New Entry!
gt;gt;gt; gt;Error Message: New entry will be added to list if you click ok.
gt;gt;gt; gt;
gt;gt;gt; gt;Then I used a worksheet_change event that waited for a change to A1. Note that
gt;gt;gt; gt;this will not work in xl97. (But Debra shows away around it using a button near
gt;gt;gt; gt;the dropdown.)
gt;gt;gt; gt;
gt;gt;gt; gt;This was the code behind sheet1:
gt;gt;gt; gt;
gt;gt;gt; gt;Option Explicit
gt;gt;gt; gt;Private Sub Worksheet_Change(ByVal Target As Range)
gt;gt;gt; gt;
gt;gt;gt; gt; Dim myList As Range
gt;gt;gt; gt;
gt;gt;gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt;gt;gt; gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt;gt;gt; gt; If Target.Value = quot;quot; Then Exit Sub
gt;gt;gt; gt;
gt;gt;gt; gt; Set myList = Nothing
gt;gt;gt; gt; Select Case LCase(Target.Address(0, 0))
gt;gt;gt; gt; Case Is = quot;a1quot;
gt;gt;gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt;gt;gt; gt; Case Is = quot;b9quot;
gt;gt;gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt;gt;gt; gt; Case Is = quot;c3quot;
gt;gt;gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt;gt;gt; gt; 'etc
gt;gt;gt; gt; End Select
gt;gt;gt; gt;
gt;gt;gt; gt; If myList Is Nothing Then
gt;gt;gt; gt; Exit Sub
gt;gt;gt; gt; End If
gt;gt;gt; gt;
gt;gt;gt; gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt;gt;gt; gt; 'already there, do nothing
gt;gt;gt; gt; Else
gt;gt;gt; gt; With myList
gt;gt;gt; gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt;gt;gt; gt; Set myList = .Resize(.Rows.Count 1, 1)
gt;gt;gt; gt; End With
gt;gt;gt; gt;
gt;gt;gt; gt; With myList
gt;gt;gt; gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt;gt;gt; gt; End With
gt;gt;gt; gt; End If
gt;gt;gt; gt;
gt;gt;gt; gt;End Sub
gt;gt;gt; gt;
gt;gt;gt; gt;It seemed to work ok.
gt;gt;gt; gt;
gt;gt;gt; gt;If you're new to macros, you may want to read David McRitchie's intro at:
gt;gt;gt; gt;www.mvps.org/dmcritchie/excel/getstarted.htm
gt;gt;gt; gt;
gt;gt;gt; gt;If you want to read more about these kinds of events:
gt;gt;gt; gt;
gt;gt;gt; gt;Chip Pearson's site:
gt;gt;gt; gt;www.cpearson.com/excel/events.htm
gt;gt;gt; gt;
gt;gt;gt; gt;David McRitchie's site:
gt;gt;gt; gt;www.mvps.org/dmcritchie/excel/event.htm
gt;gt;gt; gt;
gt;gt;gt; gt;
gt;gt;gt; gt;cjtj4700 wrote:
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; Hi~
gt;gt;gt; gt;gt; I have been hooked up with a very nice VLookup tool for my companies
gt;gt;gt; gt;gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt;gt;gt; gt;gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt;gt;gt; gt;gt; would like to know if anyone out there would be willing to send me the code
gt;gt;gt; gt;gt; or let me send them my workbook to allow new entries into any dropdown cell
gt;gt;gt; gt;gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt;gt;gt; gt;gt; there is a macro that enables this and VB does a fly by on me. The sample
gt;gt;gt; gt;gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt;gt;gt; gt;gt;
gt;gt;gt; gt;gt; Thanks as always!
gt;gt;gt;

You guys are awesome!! Thx Gord amp; Dave.
Here's what you have enabled me to create:

All Dynamic DV dropdowns now can be added to per quot;error message click okquot;
code. These are used with Employee, Carrier, Service, Description.

Employee email auto fills using =INDEX/MATCH formula.

I added a pop up calendar for the date cell with a comment informing the
user to quot;right click for calendarquot;, picks the date and calendar dissapears.

Of course I have the VLookup tool to auto fill the address lines simply by
picking the company from a dropdown Database Name box Sort on quot;Companyquot;
tool to update list.

and a =IF formula forcing users to select either quot;Businessquot; or quot;Personalquot; amp;
quot;Prepaidquot; or quot;Collectquot; in shipment type. Anything else or empty cell causes
error message.

I have been using XL for about 2 months now so thank you again Mr. Dibben,
Mr. Peterson, Mr. Phillips amp; Debra aka Contextures for helping me with this.

Happy Holidays!

quot;Dave Petersonquot; wrote:

gt; I think you should try it yourself. If you're ever going to support changes to
gt; the workbook, you're gonna want to do it yourself.
gt;
gt; In my code, A1 used a list from Sheet2 named List1
gt; B9 used List2
gt; C3 used List3
gt;
gt; The code itself goes behind the worksheet with the data|validation cells.
gt;
gt; Just rightclick on that worksheet's tab and choose View code. You'll see a code
gt; window open and you'll just paste the code there.
gt;
gt;
gt;
gt; cjtj4700 wrote:
gt; gt;
gt; gt; Thanks Dave. I am learning so much. If I sent you my actual named ranges,
gt; gt; sheet names and all pertinent locations of my spreadsheet could you plop them
gt; gt; into your code? Then I imagine I could paste it. Or I could email you my
gt; gt; spreadsheet?
gt; gt; I really appreciate your and everyones help.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt; gt; gt; for me) quot;List1quot;.
gt; gt; gt;
gt; gt; gt; I defined that name using this:
gt; gt; gt; =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt; gt; gt; So that it would expand and contract based on the number of entries in column A
gt; gt; gt; of Sheet2.
gt; gt; gt;
gt; gt; gt; Debra Dalgleish has instructions:
gt; gt; gt; www.contextures.on.ca/xlNames01.html#Dynamic
gt; gt; gt;
gt; gt; gt; Then I applied data|validation to A1 of sheet1.
gt; gt; gt; I chose List and used List1 as the range for the list.
gt; gt; gt;
gt; gt; gt; But on the Error alert tab of the Data|Validation dialog, I chose:
gt; gt; gt; Style: Warning
gt; gt; gt; Title: New Entry!
gt; gt; gt; Error Message: New entry will be added to list if you click ok.
gt; gt; gt;
gt; gt; gt; Then I used a worksheet_change event that waited for a change to A1. Note that
gt; gt; gt; this will not work in xl97. (But Debra shows away around it using a button near
gt; gt; gt; the dropdown.)
gt; gt; gt;
gt; gt; gt; This was the code behind sheet1:
gt; gt; gt;
gt; gt; gt; Option Explicit
gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt;
gt; gt; gt; Dim myList As Range
gt; gt; gt;
gt; gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; gt; gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt; gt; gt; If Target.Value = quot;quot; Then Exit Sub
gt; gt; gt;
gt; gt; gt; Set myList = Nothing
gt; gt; gt; Select Case LCase(Target.Address(0, 0))
gt; gt; gt; Case Is = quot;a1quot;
gt; gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt; gt; gt; Case Is = quot;b9quot;
gt; gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt; gt; gt; Case Is = quot;c3quot;
gt; gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt; gt; gt; 'etc
gt; gt; gt; End Select
gt; gt; gt;
gt; gt; gt; If myList Is Nothing Then
gt; gt; gt; Exit Sub
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt; gt; gt; 'already there, do nothing
gt; gt; gt; Else
gt; gt; gt; With myList
gt; gt; gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt; gt; gt; Set myList = .Resize(.Rows.Count 1, 1)
gt; gt; gt; End With
gt; gt; gt;
gt; gt; gt; With myList
gt; gt; gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt; gt; gt; End With
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; It seemed to work ok.
gt; gt; gt;
gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt;
gt; gt; gt; If you want to read more about these kinds of events:
gt; gt; gt;
gt; gt; gt; Chip Pearson's site:
gt; gt; gt; www.cpearson.com/excel/events.htm
gt; gt; gt;
gt; gt; gt; David McRitchie's site:
gt; gt; gt; www.mvps.org/dmcritchie/excel/event.htm
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; cjtj4700 wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Hi~
gt; gt; gt; gt; I have been hooked up with a very nice VLookup tool for my companies
gt; gt; gt; gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt; gt; gt; gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt; gt; gt; gt; would like to know if anyone out there would be willing to send me the code
gt; gt; gt; gt; or let me send them my workbook to allow new entries into any dropdown cell
gt; gt; gt; gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt; gt; gt; gt; there is a macro that enables this and VB does a fly by on me. The sample
gt; gt; gt; gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks as always!
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Glad you got it working (I'll speak, er, write for the others, too!)

Enjoy the season!

cjtj4700 wrote:
gt;
gt; You guys are awesome!! Thx Gord amp; Dave.
gt; Here's what you have enabled me to create:
gt;
gt; All Dynamic DV dropdowns now can be added to per quot;error message click okquot;
gt; code. These are used with Employee, Carrier, Service, Description.
gt;
gt; Employee email auto fills using =INDEX/MATCH formula.
gt;
gt; I added a pop up calendar for the date cell with a comment informing the
gt; user to quot;right click for calendarquot;, picks the date and calendar dissapears.
gt;
gt; Of course I have the VLookup tool to auto fill the address lines simply by
gt; picking the company from a dropdown Database Name box Sort on quot;Companyquot;
gt; tool to update list.
gt;
gt; and a =IF formula forcing users to select either quot;Businessquot; or quot;Personalquot; amp;
gt; quot;Prepaidquot; or quot;Collectquot; in shipment type. Anything else or empty cell causes
gt; error message.
gt;
gt; I have been using XL for about 2 months now so thank you again Mr. Dibben,
gt; Mr. Peterson, Mr. Phillips amp; Debra aka Contextures for helping me with this.
gt;
gt; Happy Holidays!
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; I think you should try it yourself. If you're ever going to support changes to
gt; gt; the workbook, you're gonna want to do it yourself.
gt; gt;
gt; gt; In my code, A1 used a list from Sheet2 named List1
gt; gt; B9 used List2
gt; gt; C3 used List3
gt; gt;
gt; gt; The code itself goes behind the worksheet with the data|validation cells.
gt; gt;
gt; gt; Just rightclick on that worksheet's tab and choose View code. You'll see a code
gt; gt; window open and you'll just paste the code there.
gt; gt;
gt; gt;
gt; gt;
gt; gt; cjtj4700 wrote:
gt; gt; gt;
gt; gt; gt; Thanks Dave. I am learning so much. If I sent you my actual named ranges,
gt; gt; gt; sheet names and all pertinent locations of my spreadsheet could you plop them
gt; gt; gt; into your code? Then I imagine I could paste it. Or I could email you my
gt; gt; gt; spreadsheet?
gt; gt; gt; I really appreciate your and everyones help.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; First, I named my list on sheet2 that was used for data|validation on sheet1 (a1
gt; gt; gt; gt; for me) quot;List1quot;.
gt; gt; gt; gt;
gt; gt; gt; gt; I defined that name using this:
gt; gt; gt; gt; =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)
gt; gt; gt; gt; So that it would expand and contract based on the number of entries in column A
gt; gt; gt; gt; of Sheet2.
gt; gt; gt; gt;
gt; gt; gt; gt; Debra Dalgleish has instructions:
gt; gt; gt; gt; www.contextures.on.ca/xlNames01.html#Dynamic
gt; gt; gt; gt;
gt; gt; gt; gt; Then I applied data|validation to A1 of sheet1.
gt; gt; gt; gt; I chose List and used List1 as the range for the list.
gt; gt; gt; gt;
gt; gt; gt; gt; But on the Error alert tab of the Data|Validation dialog, I chose:
gt; gt; gt; gt; Style: Warning
gt; gt; gt; gt; Title: New Entry!
gt; gt; gt; gt; Error Message: New entry will be added to list if you click ok.
gt; gt; gt; gt;
gt; gt; gt; gt; Then I used a worksheet_change event that waited for a change to A1. Note that
gt; gt; gt; gt; this will not work in xl97. (But Debra shows away around it using a button near
gt; gt; gt; gt; the dropdown.)
gt; gt; gt; gt;
gt; gt; gt; gt; This was the code behind sheet1:
gt; gt; gt; gt;
gt; gt; gt; gt; Option Explicit
gt; gt; gt; gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; gt; gt; gt;
gt; gt; gt; gt; Dim myList As Range
gt; gt; gt; gt;
gt; gt; gt; gt; If Target.Cells.Count gt; 1 Then Exit Sub
gt; gt; gt; gt; If Intersect(Target, Me.Range(quot;a1,b9,c3quot;)) Is Nothing Then Exit Sub
gt; gt; gt; gt; If Target.Value = quot;quot; Then Exit Sub
gt; gt; gt; gt;
gt; gt; gt; gt; Set myList = Nothing
gt; gt; gt; gt; Select Case LCase(Target.Address(0, 0))
gt; gt; gt; gt; Case Is = quot;a1quot;
gt; gt; gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list1quot;)
gt; gt; gt; gt; Case Is = quot;b9quot;
gt; gt; gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list2quot;)
gt; gt; gt; gt; Case Is = quot;c3quot;
gt; gt; gt; gt; Set myList = Me.Parent.Worksheets(quot;sheet2quot;).Range(quot;list3quot;)
gt; gt; gt; gt; 'etc
gt; gt; gt; gt; End Select
gt; gt; gt; gt;
gt; gt; gt; gt; If myList Is Nothing Then
gt; gt; gt; gt; Exit Sub
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
gt; gt; gt; gt; 'already there, do nothing
gt; gt; gt; gt; Else
gt; gt; gt; gt; With myList
gt; gt; gt; gt; .Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
gt; gt; gt; gt; Set myList = .Resize(.Rows.Count 1, 1)
gt; gt; gt; gt; End With
gt; gt; gt; gt;
gt; gt; gt; gt; With myList
gt; gt; gt; gt; .Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
gt; gt; gt; gt; End With
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; It seemed to work ok.
gt; gt; gt; gt;
gt; gt; gt; gt; If you're new to macros, you may want to read David McRitchie's intro at:
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt;
gt; gt; gt; gt; If you want to read more about these kinds of events:
gt; gt; gt; gt;
gt; gt; gt; gt; Chip Pearson's site:
gt; gt; gt; gt; www.cpearson.com/excel/events.htm
gt; gt; gt; gt;
gt; gt; gt; gt; David McRitchie's site:
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/event.htm
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; cjtj4700 wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi~
gt; gt; gt; gt; gt; I have been hooked up with a very nice VLookup tool for my companies
gt; gt; gt; gt; gt; shipping request form (thank you Gord!). Several quot;Listsquot; of data is stored in
gt; gt; gt; gt; gt; Sheet2, most used for the company Vlookup, other as simple DV dropdown. I
gt; gt; gt; gt; gt; would like to know if anyone out there would be willing to send me the code
gt; gt; gt; gt; gt; or let me send them my workbook to allow new entries into any dropdown cell
gt; gt; gt; gt; gt; to be auto added to its source list, alphabetically. Per Contextures.com,
gt; gt; gt; gt; gt; there is a macro that enables this and VB does a fly by on me. The sample
gt; gt; gt; gt; gt; spreadsheet I was looking at was quot;Update Multiple Validation Listsquot;.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks as always!
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson