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

 

[art33]

Garys Student wrote on 03/02/2010 09:59 ET

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

Say B1 thru C5 contain

Name I
Larry 1
Moe 1
Curly 1
Shep 2

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

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

v = A1.Valu
A1.Clea
For Each r In Range("B:B"
If r.Value = v The
A1.Value = r.Offset(0, 1).Valu
Exit Fo
End I
Nex
Application.EnableEvents = Tru
End Su

Once the name has been selected, the cell is cleared (including th
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 windo
2. select View Code - this brings up a VBE windo
3. paste the stuff in and close the VBE windo

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

To remove the macro

1. bring up the VBE windows as abov
2. clear the code ou
3. close the VBE windo

To learn more about macros in general, see

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

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

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

Gary''s Student - gsnu20100


"StephenT" wrote

Sorry, could you please explain how do I actually modify the range so it woul
be, say "A1:A45"
Many thanks
A.