Drop down / validation / lookup into the same cell

[StephenT]

Hi

I am using 2007 and would like to drop down from a list in a cell and have a
lookup in the same cell

eg. I have a list of employees and their numbers = emp_list and emp_number

emp_list emp_number
Joe 1
Jane 2
Bob 3


I want users to be able to select employees from a list then the model will
look up the employee number IN THE SAME CELL.

Now, I know how to get a drop down list of employees using date validation =
emp_list. And I know how to lookup up the emp_number in a seperate cell using
INDEX(MATCH()) or VLOOKUP.

But I have many columns of these so I would like to be able to have the
drop-down and the number in the same cell.

so, In A1, there would be a drop down which had the emp_list. When the
employee is selected, the value in A1 would be the employee number.

I need to drag and drop over thousands of records so it needs to be in-cell
(no in a seperate drop-down box)

Is this possible?

Thanks
s

 

[Jim Thomlinson]

The short answer is no...

 

[Gary''s Student]

This example is for cell A1, it can easily be modified to include any set of
cells.

Say B1 thru C5 contain:

Name ID
Larry 13
Moe 17
Curly 19
Shep 23

and the data validation covers A2 thru A5. Insert the following event macro
in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A1 As Range
Set A1 = Range("A1")
Set t = Target
If Intersect(A1, t) Is Nothing Then Exit Sub
Application.EnableEvents = False

v = A1.Value
A1.Clear
For Each r In Range("B:B")
If r.Value = v Then
A1.Value = r.Offset(0, 1).Value
Exit For
End If
Next
Application.EnableEvents = True
End Sub

Once the name has been selected, the cell is cleared (including the
validation rule) and the equivalent ID is placed in A1.

Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm