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

 

[flashgordie]

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!

Report7.xlsm
	A	B
1	Facility name
2	Location Identifier
3	Contact Name
4	Primary Phone 1
5	email:
6	Alt Contact Name
7	Alt Phone 2
8
9	Name	Initials
10	++ Blank	Blank
11	Maximo Romeo	MR
12	Suellen Ledezma	SL
13	Ying Krell	YK
14	Trish Weil	TW
15	Penni Schillinger	PS
16	Eleonore Butts	EB
17	Charlie Ussery	CU
18	Alta Vanhorne	AV
19	Marianne Flemings	MF
20	Hung Ku	HK
21	Hannelore Cray	HC
22	Joette Blaine	JB
23	Glory Almonte	GA
24	Grover Capobianco	GC
25	Jake Smart	JS
26
DataSource

Cells with Data Validation
Cell	Allow	Criteria
B9	Custom	=COUNTIF(#REF!,#REF!)=1
B10:B25	Custom	=COUNTIF(initials2,B10)=1

Report7.xlsm
	A	B	C
58		List
59
60
61		Onset Date YYYY-MM-DD	NAME / ID
62	1	2020-09-26	++ Blank
63	2	2020-09-27
64	3	2020-09-28
65	4	2020-09-29
66	5	2020-09-30
67	6	2020-10-01
68	7	2020-10-02
69	8
70	9
71	10
72	11
73	12
74	13
75	14
76	15
77
Oct3-Oct9

Cells with Data Validation
Cell	Allow	Criteria
B62:B76	Date	between $H$4 and $K$4
C62:C76	List	=INDIRECT("TableName[Name]")

 

[Fluff]

You said the DV was from C63, but that shows it starts in C62, so try

   If Not Intersect(Target, Range("C62:C76")) Is Nothing Then

 

[flashgordie]

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!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Tom.Jones]

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.

 

[Fluff]

@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]

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]

TableNmae is the name of the table, not a named range.