data validation lists

[Guest]

Hi,
I am trying to create a list of responses that would be displayed to a user
via a drop down list and when a response is selected only a short version (eg
3 letter code) would be displayed in that cell.

My initial thought were:
Create a list called CODE eg
CAR CArdiovascular
DEV Developmental
DSY Down’s syndrome
EYE Eye Nystagmus
GAS Gastrointestinal

I was hoping that I would be able to use Data Validation option with some
formula in the list option, eg =left(CODE,3).
But this does not work
Any ideas?

 

[Guest]

Good Evening Andy
your on the right track, the formula is simplistic. You will need to create
two seperate rows of information, 1st the 3 letter abrlist, second the long
name list,
create one dropdown and header name for the 3 letter list. ($A$2:$A$6)
create header name and place this formula in the cell below the header
=IF(C13=C2,G2:H2,IF(C13=C4,G4:H4,IF(C13=C6,G6:H6,IF(C13=C8,G8:H8,IF(C13=C10,G10:H10,$A$1)))))
I use C13 as my answer to the three letter dropdown and the words are
collected at each of the cell references. If you have any trouble or
questions please post them
it should look like this
DSY Down’s syndrome (each time you click a different abv, long name)

 

[Dave Peterson]

Personally, I'd use two cells. The cell with data|validation would contain the
long string and the adjacent(?) cell would contain the short string

Put the table in Sheet2 (long name in column A, short name in column B).

A1 has the data|validation cell
B1 has this formula:
=if(a1="","",vlookup(a1,sheet2!a:b,2,false)

Then I could use either cell in any other formulas.

But if you're using xl2k or higher (won't work in xl97), you could use the same
table and a worksheet_event that overwrites the value in the cell with the short
name.

You'll have to name that range in column A for Data|Validation to work with a
list on a different worksheet.

Debra Dalgleish explains it:
http://contextures.com/xlDataVal01.html#Name

If you want to try...

I used Sheet2 and created a list named myList.

Then right click on the worksheet tab that should have this behavior and select
view code. And paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
Dim myList As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If

If Target.Value = "" Then Exit Sub

Set myList = Worksheets("sheet2").Range("myList")
res = Application.Match(Target.Value, myList, 0)

If IsError(res) Then
'this shouldn't happen!
MsgBox "Something bad happened"
Else
Application.EnableEvents = False
Target.Value = myList(res, 2).Value
Application.EnableEvents = True
End If

End Sub


Then change "a1" to the cell that has the data|validation.

And back to excel to test it.

 

[Guest]

Another play to achieve it would be
to use a combo box from the control toolbox toolbar

Try this sample from my archives which illustrates:
http://savefile.com/files/221672
Combo box from control toolbox toolbar_Example2.xls

 

[Guest]

Thanks. This solved my issue.

: