Data Validation and IF Then Statements

Microsoft Microsoft office excel 2007 ac...
May 12, 2011 at 12:53:35
Specs: Windows Vista
Hey, it has been a while for with me with programing detailed within Excel (still trying to shake the spiderwebs off). I have an Employee Database that my company is utilizing in spreadsheet format at the moment. I am trying to remember how to do the following:

Sheet Name: Employee List
Column A = Employee Name
Column B = Employee Nickname
Column C = Employee ID

for scheduling purposes, they want to select Column A in the data validation, however have it return the nickname.

Sheet Name: Schedule A
Column A = Data List(select Employee Name, return value of Employee Nickname).

I know this is a beginners question, but hey.. everyone forgets something from the basics every once in a while.. lol

Thanks, Irish

See More: Data Validation and IF Then Statements

May 12, 2011 at 21:04:30
I'm confused.

To where do you want to return the nickname? In the same cell where they chose the employee from the Data Validation drop down?

If that's the case, it's far from a beginner's question.

You would need to use VBA to replace the name that was chosen with the corresponding nickname.

If you want the nickname in some other cell, then it's much simpler: Use VLOOKUP to pull the nickname from the table on the Employee List sheet.

Which of those two are looking to accomplish?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 12, 2011 at 21:30:13
Looking for same cell placement, not VLOOKUP. Thanks

Report •

May 12, 2011 at 21:48:42
Then you are willing to accept a VBA solution?

If so, it'll still be VLOOKUP, but it will be done in VBA.

Let's say the Data Validation drop down where the names will be chosen is in Schedule A!A1

Let's say the list of employee names is in Employee List!A1:A10 with the nicknames in Employee List!B1:B10.

Use this code in the VBA sheet module for Schedule A:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" Then
  Application.EnableEvents = False
   MyNick = _
    Application.WorksheetFunction.VLookup _
      (Target, Sheets("Employee List").Range("A1:B10"), 2, False)
    Target = MyNick
  Application.EnableEvents = True
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

May 15, 2011 at 18:31:23
ty DerbyDad. I had thought it dealt with VLOOKUP but I couldn't connect the dots on it - so to speak. Thank you very much.

Report •

Ask Question