return alternate value based on dropdown selection - Data Validation

[flashgordie]

Hello All, I hope I am explaining this correctly - I have a workbook that contains 2 sheets.

On Sheet 1, I have my data – employee name, and employee intials. These have each been defined using tables and will be part of a data validation process on sheet two; this also allows the table range to expand as new employees (and initials) to ensure the dropdowns I have on sheet 2 continue to populate with all the data. The employee initials table has a custom validation, with error message to ensure that the number I assign is unique.

My question, On sheet two, I have a dropdown showing each employees name, however, once he name is selected from the drop down, I would like it to populate the cell with the employees inititals from the initials table. Is this possible?

As always, Thanks very much for you insight.

Sheet 1

Sheet 2
has the employee name dropdown, but when I select the employee, like Penni Schillinger, I would like it to return her initials found in the initials table - "PS".

 

[Fluff]

The easiest option would be to put a lookup formula in the next cell to return the initials.

 

[flashgordie]

The easiest option would be to put a lookup formula in the next cell to return the initials.

Thanks. The issue is that in the end on the second sheet... will be printed and for confidentiality reasons I can't have the names showing in it. Thanks for the reply though!

 

[Fluff]

Can't you just hide the Employee column before printing?

 

[flashgordie]

Unfortunately no. the sheet is going out to a number of branches where the HR people will be tracking occurances for each employee. This Data will then be sent to a third party to be analyzed. In the end when we get the data back we need to be able to identify the individuals, but we can not share their names outright.

Sidebar - We could assign a employee number instead of their initials but then when we get the data back we would need to filter that back out but that still would not help having the dropdown with the proper name being switched to their identifier (initials) on sheet 2.

Again. Thanks for your interest in helping me. It is appreciated.

 

[Fluff]

In that case I think your only option would be a macro to automatically change the data when it's entered.
Is that ok with you?

 

[flashgordie]

Hello, Yes, I would certainly be willing try it. I am not much of a VBA coder, but I have been trying muddle thorough some code I found.

Private Sub Worksheet_Change(ByVal Target As Range)
selectedNa = Target.Value
If Target.Column = 5 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("confidential"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub



When testing this I selected my two columns (Name and Initials) and defined them as “confidential”. I could get it to work in practice but it is a little buggy depending on the set up. I figure it might have a couple more issues as I would like my dropdown to start in cell c63 where this script simply specifies the fifth column ("E"). Another issue I think is that this seems to work when the source data and the dropdown are on the same sheet. My source data for “confidential” is on sheet one, where my dropdown is on sheet two. I therefore figure I might need to change the sheet range as well.



I am certainly open to other code or ideas you might have. Certainly thanks again for the help!

 

[Fluff]

Ok, a few questions.
Which cells have the data validation that you wish to change?
What is the name of the sheet that has the names/initials?
Does this need to work whenever one of the data validation changes, or would it be better to run it manually before sending it out to the 3rd party?

 

[flashgordie]

Hello Again,

Sheet name 1: is titled “DataSource” and here resides my table my TableName A9:A? and TableInitials B10:B? Both of these ranges expand as more names/initials are added.

Sheet name 2: is titled “Oct3-Oct9”. however, we may need to add additional sheets for other periods. The data validation for the dropdown containing the names which I would like to have switched to the initials is in each of the cells from c63:c76.

I would prefer it to work up once the name is selected from the dropdown that it switches it to the initials immediately.

Thank you for your continued interest and assistance.

 

[Fluff]

Ok how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C63:C76")) Is Nothing Then
      Set Fnd = Sheets("DataSource").Range("TableName").Find(Target.Value, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then Exit Sub
      Application.EnableEvents = False
      Target.Value = Fnd.Offset(, 1).Value
      Application.EnableEvents = True
   End If
End Sub