return alternate value based on dropdown selection - Data Validation

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
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

names and initials.png


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".

names and initials2.png
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
The easiest option would be to put a lookup formula in the next cell to return the initials.
 

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
Can't you just hide the Employee column before printing?
 

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 9, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jan 9, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
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.
a.png


c.png


b.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
Ok how about
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,847
Messages
5,598,427
Members
414,237
Latest member
tereres

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top