Entering a number that converts to text

**andrewmcnabb** · 11-19-2012, 06:05 AM

This is my first post, so I trust I make myself clear!

I am entering a large amount of data into a spreadsheet. One of the rows is titled 'Operative', where a staff name is entered. Would it be possible if I were to assign each member of staff a number (e.g. 04), when I enter that number into the cell, it automatically changes that number to the staff member's name (e.g. Mark).

I hope you understand what I mean, and can see the benefits of this, saving me typing more characters than necessaries into each row!

Any help would be appreciated.

Andy

**patel45** · 11-19-2012, 06:31 AM

Use data validation http://www.contextures.com/xlDataVal01.html

**nested.if1@gmail.com** · 11-19-2012, 06:40 AM

I think vlookup might help....

Create a master of emp name and emp code and then use lookup. Please find attached the sample file for reference.

**Bishonen** · 11-19-2012, 06:52 AM

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Value

Case Is = 1
Target.Value = "John"
Case Is = 2
Target.Value = "Tom"
End Select

End Sub

this goes into the worksheet module of the affected worksheet.

Not really sure how to make it work for numbers with two preceeding zeros. The downside of this code is that you wont be able to enter either 1 nor 2 in the worksheet.

You could alter it to do those changes only in a desired range:


Private Sub Worksheet_Change(ByVal Target As Range)

set mRange = Range("A1:A20")

if not intersect(mRange,Target) is nothing then

Select Case Target.Value

Case Is = 1
Target.Value = "John"
Case Is = 2
Target.Value = "Tom"
End Select

end if

End Sub

**Patnaik** · 11-19-2012, 07:52 AM

@andrew

Hi

Firstly Welcome to this Forum

i agree with patel45 it is better and simple for you

other wise you have to create on Name Range and in the name you have to mention the Names then afer u can use the below formula

for A1 IN B1

=IF(A1="","" ,INDEX(DEALER_NAME,A1))

With the Above Formula when you enter the No. 1 then in b1 It will display the Name

Hope it will work, other wise please inform us with a sample Workbook

Thanks

Patnaik