Complex (to me) drop down menu

[Elysha]

I have created a time sheet for my employees in Excel. On the second sheet I have a table that lists the “Client”, “Project Name” and “Project #”. I need to create a drop down in the time sheet where we can select the “Client” then auto populate the other columns. The challenge here is that a client is listed more than once with multiple projects. How do I go about doing this? Thank you in advance for any help you are able to provide.

 

[SanjayGMusafir]

I have created a time sheet for my employees in Excel. On the second sheet I have a table that lists the “Client”, “Project Name” and “Project #”. I need to create a drop down in the time sheet where we can select the “Client” then auto populate the other columns. The challenge here is that a client is listed more than once with multiple projects. How do I go about doing this? Thank you in advance for any help you are able to provide.

First create a list using UNIQUE Function - Then use that list for data validation (Drop Down List)

 

[Elysha]

I have created a time sheet for my employees in Excel. On the second sheet I have a table that lists the “Client”, “Project Name” and “Project #”. I need to create a drop down in the time sheet where we can select the “Client” then auto populate the other columns. The challenge here is that a client is listed more than once with multiple projects. How do I go about doing this? Thank you in advance for any help you are able to provide.

Additionally, within the Client list, I need to alphabetize the results.

 

[SanjayGMusafir]

Additionally, within the Client list, I need to alphabetize the results.

Put UNIQUE Function within SORT function the list thus populated will be sorted.

 

[Elysha]

First create a list using UNIQUE Function - Then use that list for data validation (Drop Down List)

Employee Name:		September	2022​
					19	20	21	22	23	24	25
Job Name	Work Done - Specifics	Client	Job #	M	TU	W	TH	F	S	S	Total
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
												0
			Total Time		0	0	0	0	0	0	0	0	0​

 

[Elysha]

Above was the time sheet. Here I have an example of the 300 lines of my table for the items to go into the time sheet. They are on separate sheets in my file. I can't seem to get this to work.

Project #	Project	Client
22-1	Hyundai	ABC Company
22-2	Funeral Home	DEF Company
22-3	Mazda	ABC Company
22-4	Elementary School	GHI Company
22-5	Subaru	ABC Company

 

[SanjayGMusafir]

Check this -

Use SheetName!E2# for data Validation

All Records.xlsb
	A	B	C	D	E
1	Project #	Project	Client		Clients
2	22-Jan	Hyundai	ABC Company		ABC Company
3	22-Feb	Funeral Home	DEF Company		DEF Company
4	22-Mar	Mazda	ABC Company		GHI Company
5	22-Apr	Elementary School	GHI Company
6	22-May	Subaru	ABC Company
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=SORT(UNIQUE(C2:C6),,1)
Dynamic array formulas.

 

[Elysha]

Check this -

Use SheetName!E2# for data Validation

All Records.xlsb
	A	B	C	D	E
1	Project #	Project	Client		Clients
2	22-Jan	Hyundai	ABC Company		ABC Company
3	22-Feb	Funeral Home	DEF Company		DEF Company
4	22-Mar	Mazda	ABC Company		GHI Company
5	22-Apr	Elementary School	GHI Company
6	22-May	Subaru	ABC Company
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=SORT(UNIQUE(C2:C6),,1)
Dynamic array formulas.

Thanks Sanjay! I have the Clients and the Project Name working. I can't seem to get the Project #. I have =FILTER(Table24[Project],Table24[Project Number]=A4)

I'm receiving the #CALC error.

 

[SanjayGMusafir]

=FILTER(Table24[Project],Table24[Project Number]=A4)

Check if this is correct - Table24[Project Number]

Can't think of any other reason right now...