Help! How Do I Do This???

[mommatee]

Each person in my dept (approx 10 people) are assigned codes that they are
responsible for. For example: John Smith's codes are 080, 116P and 114.
Jane Doe's codes are 115AB, 11D, 10PI and 1586D, etc.) If someone needs to
get information on someone else's code, we send them an email. To eliminate
having to send emails, I'd like to create a shared worksheet that allows each
of us to type the code needing information in column A. I'd like the cells in
column B to automatically fill in the person that's responsible for the code
typed in column A.

So, if I type the following in column A, it will provide me with info in
column B
(A1) 11D (B1) Jane Doe
(A2) 116P (B2) John Smith

This will tell Jane Doe to provide whatever info is needed on her code 11D
and John Smith will provide whatever info is needed on his code 116P.

I basically want to do something like conditioning formatting.

Thanks for any help anyone can give me.

 

[Ed from AZ]

Maybe dependant lists using Data Validation?
http://www.contextures.com/xlDataVal01.html

Ed

 

[Office_Novice]

Somthing like...

Option Explicit
Sub Do_Cool_Stuff()
'Declare your Variables
Dim aRange As Range
Dim John_Smith
Dim Jane_Smith
Dim i

'Tell Excel what they are
Set aRange = Range("A1:A50")

John_Smith = Array("080", "116P", "114")

Jane_Smith = Array("115AB", "11D", "10PI", "1586D")


'Tell excel what to do when it finds them

For Each i In aRange
If i.Value = John_Smith(0) Then
i.Offset(0, 1).Value = "John Smith"
ElseIf i.Value = John_Smith(1) Then
i.Offset(0, 1).Value = "John Smith"
ElseIf i.Value = John_Smith(2) Then
i.Offset(0, 1).Value = "John Smith"
ElseIf i.Value = Jane_Smith(0) Then
i.Offset(0, 1).Value = "Jane Smith"
ElseIf i.Value = Jane_Smith(1) Then
i.Offset(0, 1).Value = "Jane Smith"
ElseIf i.Value = Jane_Smith(2) Then
i.Offset(0, 1).Value = "Jane Smith"
ElseIf i.Value = Jane_Smith(3) Then
i.Offset(0, 1).Value = "Jane Smith"
End If
Next
End Sub

 

[mommatee]

Thanks, but Data Validation won't work. There are approx 100 different codes
and would require the person requesting information to know the name of the
person that's assigned to the code.