#1   Report Post  
metrueblood
 
Posts: n/a
Default Dropdown lists

Hi! Here's my situation... In a worksheet, within a column, repetitive data
can be entered. So to speed things along, i've created lists, and then
applied data validation to the column. My question is how can i get the drop
down lists to automatically expand when a new choice (one not in the current
list) is entered, and then be a choice from there on? In my creation of the
list i have used the OFFSET fxn, and if i type the new choice in the list
worksheet, the dropdown list automatically updates. What i'd like is for it
to be entered on the building spreadsheet and then update to the list
worksheet and be a choice in the dropdown list from that point. Thanks for
any help you might be able to give!!!! mary trueblood :-)
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

If you're using xl97, this won't work.

But if you're using xl2k or better, you could use a worksheet event that updates
that other list.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myVRng As Range
Dim res As Variant

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("e1:e30")) Is Nothing Then Exit Sub

Set myVRng = Worksheets("sheet2").Range("mylist")

res = Application.Match(Target.Value, myVRng, 0)
If IsError(res) Then
'not on the list yet
With myVRng
.Cells(1).Offset(.Rows.Count, 0).Value = Target.Value
With .Resize(.Rows.Count + 1, 1)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlNo
End With
End With
End If

End Sub

I put my list in Sheet2: A1:A?? (no header!)
I used this formula in insert|define to create that name "myList":
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

I made the Data|validation error alert a warning with this text in the message:

If you accept this entry, it will be added to the Data|Validation list for this
range.



metrueblood wrote:

Hi! Here's my situation... In a worksheet, within a column, repetitive data
can be entered. So to speed things along, i've created lists, and then
applied data validation to the column. My question is how can i get the drop
down lists to automatically expand when a new choice (one not in the current
list) is entered, and then be a choice from there on? In my creation of the
list i have used the OFFSET fxn, and if i type the new choice in the list
worksheet, the dropdown list automatically updates. What i'd like is for it
to be entered on the building spreadsheet and then update to the list
worksheet and be a choice in the dropdown list from that point. Thanks for
any help you might be able to give!!!! mary trueblood :-)


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing 2 Customer Lists to Identify Shared Customers carl Excel Worksheet Functions 2 January 26th 05 08:17 PM
how do I add data validation dropdown lists to a Form SteveD.IFlora Excel Worksheet Functions 3 January 21st 05 05:48 PM
how to increase the font size in a dropdown list? Sossys Excel Worksheet Functions 2 December 16th 04 02:09 PM
dropdown value from range in other sheet mango Excel Worksheet Functions 11 December 2nd 04 04:19 AM
how to make dropdown arrow disappear in vba mango Excel Worksheet Functions 2 November 30th 04 01:56 AM


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"