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 <gorddibbATshawDOTca wrote:

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 "New". 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.


Gord


On Wed, 14 Dec 2005 14:01:41 -0800, "cjtj4700"
wrote:

Hi Gord~
Thx. I will plug away at this and see what happens Regarding new entries,
users not finding their "Company", 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 "range", "target" and "address" 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......

"Gord Dibben" wrote:

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 "Carriers" and
"Services" which are of irregular length and sorted independently.

BUT.......being able to add to and sort the "Company" 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 DataData Form and your Database will be selected. Click on "New" and
add all the pertinent information in the appropriate dialog boxes.

Close the Form then click on Name Box and "Database". DataSortSort on
"Company" will sort all columns together in that range.


Gord


On Wed, 14 Dec 2005 10:01:23 -0600, Dave Peterson
wrote:

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

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:
http://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 1 Then Exit Sub
If Intersect(Target, Me.Range("a1,b9,c3")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

Set myList = Nothing
Select Case LCase(Target.Address(0, 0))
Case Is = "a1"
Set myList = Me.Parent.Worksheets("sheet2").Range("list1")
Case Is = "b9"
Set myList = Me.Parent.Worksheets("sheet2").Range("list2")
Case Is = "c3"
Set myList = Me.Parent.Worksheets("sheet2").Range("list3")
'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:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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

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

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm


cjtj4700 wrote:

Hi~
I have been hooked up with a very nice VLookup tool for my companies
shipping request form (thank you Gord!). Several "Lists" 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 "Update Multiple Validation Lists".

Thanks as always!