Using Drop Down List or Combo Box

[Dana M]

I not sure how to do this - if I can use Data Validation with a Drop Down
List - or Combo Box - to get this result:

I have a list of 3 selections. I want only the identifying letter in column
1 to show in the cell after selection. However, I want the user to see the
descriptive text also when holding down the selection arrow. The list will
be typed in a hidden area of the worksheet and will be used for drop downs in
3 other sheets in the workbook. These identify expenses, and will be in
column A of every row on the sheet.
Example of list text:
O Merger One Time Expense
I Merger Integration Expense
N Non-Merger Related

 

[Eduardo]

Hi Dana,
Check Debra web

http://contextures.com/xlUserForm02.html

 

[T. Valko]

There is a sample file here that does what you want:

http://contextures.com/excelfiles.html#DataVal

Look for DV0004 - Data Validation Change

 

[Dana M]

Wow, thank you - this is a very interesting procedure - but probably more
complex than I need or can follow. I'm not creating a database - more like
identifying expense lines on a spreadsheet.

 

[Dana M]

I think this is very close to what I want. However, I can't make it work. I
copied the code into a VBE module in my workbook. My workbook is a template
for budget analysts. It has several sheets with other drop down boxes and a
lot of underlying macros. However the requirements for this set of drop down
boxes was different, in that the request was to capture the letter "code" in
the cell, but show the Expense Type Name when the box was selected, as in
your suggested solution.

The underlying code in the solution is:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

Since my drop down boxes are in column "C", I changed
If Target.Column = 2 Then TO
If Target.Column = 3 Then

Do I need to assign a macro to the drop down boxes, or how do I activate the
code? The instructions seem to indicate that when I make a selection, it
triggers an event - that's not happening. After I select the Expense Type,
it shows the Expense Type, not the Expense ID.

 

[T. Valko]

That is a worksheet event macro. It doesn't go into a general module. Select
the sheet where you want this happen. Right click the sheet tab and select
View code. Paste the code into the window that opens.

 

[Dana M]

OK, thanks. I will need it to work on several worksheets. So will it be OK
to paste the code into each of the sheets?

 

[T. Valko]

It should be ok.

--
Biff
Microsoft Excel MVP

OK, thanks. I will need it to work on several worksheets. So will it be
OK
to paste the code into each of the sheets?

 

[Dana M]

Thanks so very much for a helpful solution and follow-up answers.

 

[T. Valko]

You're welcome. Thanks for the feedback!