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
 

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Hello, Thanks for your efforts but unfortunately that did not work. It is probably something that I am failing to communicate or perhaps even doing.. Below are two clips using the xl2bb addin. If you are willing to try again, I would appreciate it.

I copied and pasted the code into the the VBA side of things under sheet 2 (Oct3-Oct9)

Please let me know if there is anything else you think I could clarify that would help. Again, Thank you!

1601384666571.png



Report7.xlsm
AB
1Facility name
2Location Identifier
3Contact Name
4Primary Phone 1
5email:
6Alt Contact Name
7Alt Phone 2
8
9NameInitials
10++ BlankBlank
11Maximo RomeoMR
12Suellen LedezmaSL
13Ying KrellYK
14Trish WeilTW
15Penni SchillingerPS
16Eleonore ButtsEB
17Charlie UsseryCU
18Alta VanhorneAV
19Marianne FlemingsMF
20Hung KuHK
21Hannelore CrayHC
22Joette BlaineJB
23Glory AlmonteGA
24Grover CapobiancoGC
25Jake SmartJS
26
DataSource
Cells with Data Validation
CellAllowCriteria
B9Custom=COUNTIF(#REF!,#REF!)=1
B10:B25Custom=COUNTIF(initials2,B10)=1



Report7.xlsm
ABC
58List
59
60
61Onset Date YYYY-MM-DDNAME / ID
6212020-09-26++ Blank
6322020-09-27
6432020-09-28
6542020-09-29
6652020-09-30
6762020-10-01
6872020-10-02
698
709
7110
7211
7312
7413
7514
7615
77
Oct3-Oct9
Cells with Data Validation
CellAllowCriteria
B62:B76Datebetween $H$4 and $K$4
C62:C76List=INDIRECT("TableName[Name]")
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
You said the DV was from C63, but that shows it starts in C62, so try
Rich (BB code):
   If Not Intersect(Target, Range("C62:C76")) Is Nothing Then
 

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Yes, I did say C63, and yes, you are quite right, it is c62. My apologies' A a very big round of applause from me to you and a heart felt thank you! That look like it works well. I will give it some testing but it seems to be perfect. I have no idea what the code is doing other than it is doing what I was looking for . THANK YOU!!!!
Cheers!(y)(y)(y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi,

You have to change name of table in VBA code.
Set Fnd = Sheets("Foaie81").Range("TableName").Find(Target.Value, , , xlWhole, , , False, , False).... with your real table name.
If you say that now is OK you already changed.

1601384666571-png.23289
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
@Tom.Jones
If you had read the thread, you would realise that the table name is correct & therefore does not need to be changed.
 

Tom.Jones

Active Member
Joined
Sep 20, 2011
Messages
330
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ooooopppppssss!!!!!

I'm not a programmer, I just assumed that the table name had to be changed ... I'm sorry.
What should the code look like, accessing that table directly, without name range?
I used your VBA code with this line :
Set Fnd = Sheets("Data").Range("Table1"). Find...... and it's working.

Fluff, thousands of apologies for allowing me to comment.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
TableNmae is the name of the table, not a named range.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,847
Messages
5,598,435
Members
414,238
Latest member
juxion

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