Excel data validation using List to bring back a different value

[Wally0]

Data validation using LIST brings back a "named" range of values. This is one way to "select" a value to place into the cell. My list of "values' are text strings that can be long and difficult to read. What I would like to do is to offer the user a abbreviated text or number and fill the cell with the entire string. Here's an example:

ShortDescription LongDescription
orange An Orange Color Thingy
red A Red Color Thingy
blue A Blue Color Thingy
black A black Color Thingy

What the data validation list shows the user is:
Orange
Red
Blue
Black

What I want the selection to do is to bring back the long description. I know this can be down with a form or using one column to select the information and another to display the correct information (using the INDIRECT or Vlookup Functions). What I want to know is if this can be done using a function(s) in the data validation list. Thanks in advance for any help or direction on this.

Chris

 

[ETID]

Here is an example of how to do what you want,

http://www.contextures.com/DataValNameID.xls

....you can see on the second page where it is pulling the valid value. This example makes a long selection into a short one ....you need only reverse the items in the columns on the second sheet for you scenerio.

 

[Wally0]

A BIG Thanks ETID for the pointer and link.. This will work fine. Do you have any suggestion for expanding this to other columns? Your example fires whenever something is changed in the worksheet. Is there a method to tigger other subs (to swap values) if there are a series of columns with data valiation lists? Thanks agian for your help.

Chris

 

[ETID]

Do you mean repopluate the lookup list, based on the current column selected?

 

[Wally0]

No, I mean as in your example to expand it to have several columns with different "sets" of information. My first thought is to use the column property to test what column the user is in and then use your example code with the appropiate named fields, ranges for the selected column.

Thanks for your assistance with this.

 

[ETID]

'maybe something like this....in the event code you'll need to tweak


Select Case ActiveCell.Column

Case Is = 2

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Codes&quot.Range("C1&quot _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes&quot.Range("ProdList_1&quot, 0), 0)
Application.EnableEvents = True
End If


Case Is = 3

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 3 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Codes&quot.Range("D1&quot _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes&quot.Range("ProdList_2&quot, 0), 0)
Application.EnableEvents = True
End If

'etc....

Case Else

End Select

 

[Wally0]

ETID, thanks for this suggestion. This is exactly what I was looking for. I would have like to do this without VB code. But, I think this was the only way to go.

Chris