Display specific text based on a value within the same cell??

[realsongs]

Hi all,

Is it possbile to convert one text to another based on a set of values, all in the same cell???

For example, if I would like to type in an employees staff number is cell A1, and then for the text in cell A1 to display the corresponding staff members name.

I spent the past hour looking this up, and thought I came close with conditional formatting however the method I found doesn't seem to work.

Thank you in advance for looking!!!!

S

 

[jim may]

Excel 2010

	A	B	C	D	E	F	G	H	I
Fname	Lname
Peter	Smith
Paul	Long
Mary	Parker

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Emp#[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Post the Code (below) into Sheet1 Module (as Event Code). This worked for me....
Type either 100, 200, or 300 into Cell A1 and Enter.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
    OV = Target.Value
    Application.Undo
On Error Resume Next
Target.Value = WorksheetFunction.VLookup(OV, Range("$G$2:$I$4"), 2, False) & " " & WorksheetFunction.VLookup(OV, Range("$G$2:$I$4"), 3, False)
Application.ScreenUpdating = True
Application.EnableEvents = True
On Error GoTo 0
End Sub

 

[realsongs]

Many thanks for this, it worked perfectly.

I will modify the code to include a range of columns.

Thanks again!

 

[jim may]

At Present you should be able to Fill multiple cells in Column A (A1,A2,A3,A4.....)
It close, but not perfect... Thanks for the response. Jim