Dependant Dropdown or Macro Required?

[Gooby]

Hi

I have a 'Data Source' sheet with the header "Client Name" in cell A7. The client names start from A8 onwards.
There is a "Project ID" header in cell B7. The project IDs starts from B8 onwards.

I want to keep adding to these categories, so the range is not fixed. Screenshots below.

I want two dropdowns (Client & Project ID) on a separate sheet titled 'PSF Index Summary'.
The Client dropdown will be located in cell B2 on this second sheet and pull the information from 'Data Source' cell A7 onwards. The Project ID dropdown will be located in cell B3 below the first dropdown. I want the Project ID dropdown to be depedant on the client name selected from the first dropdown. The data for the Project ID dropdown will come from 'Data Source' cell B7 onwards.

Essentially, I want the Client dropdown to only show one instance of a client's name even though the name may appear more than once in the Data Source sheet. I also want the Project ID dropdown to only show Project IDs that are for the selected client in cell B2.
Unfortunately, due to the range being open and client names repeating, I cannot get the dropdown lists to work the way I want them to. Would a macro be able to do the job or can this be done without one? Solutions either way are welcome.

Additional notes:

• Clients appear more than once in the Data Source sheet, but with a different Project ID
• Client Names can be longer than one word
• The information in the Data Source tab is not sorted/filtered
• Unable to remove client name duplicates due to other columns in the 'Data Source' sheet with information that rely on a separate row existing

The issues I have encountered are:

• Client names repeat on the client name dropdown list
• Only the first project ID for the selected client appears
• The Project ID dropdown does not seem to work for client names that are longer than 1 word.
• Empty spaces in both dropdown lists

I have searched Youtube, Google, and even tried AI chats to no avail so thought I would try here. Any help would be greatly appreciated and please let me know if further information is required, thank you.

Data Source Screenshot

PSF Index Summary Screenshot

 

[Peter_SSs]

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this could work for you set up two formulas like I have shown in D8:E8 below (values in E8 & below will only appear once Client has been chosen in B2 on the PSF sheet). These columns could be hidden and/or could be on PSF Index Summary or even on a separate worksheet.

Gooby.xlsm
	A	B	C	D	E
7	Client	Proj ID		Client	Proj ID
8	Client 1	1		Client 1	4
9	Client 2	4		Client 2	2
10	Client 3	5		Client 3
11	Client 2	2
12	Client 3	3
13
Data Source

Cell Formulas
Range	Formula
D8:D10	D8	=UNIQUE(FILTER(A8:A100,A8:A100<>""))
E8:E9	E8	=UNIQUE(FILTER(B8:B100,(B8:B100<>"")*(A8:A100='PSF Index Summary'!B2),""))
Dynamic array formulas.

Then the Data validation lists could be set up like this

Gooby.xlsm
	A	B
1
2	Client	Client 2
3	Proj ID
4
PSF Index Summary

Cells with Data Validation
Cell	Allow	Criteria
B2	List	='Data Source'!$D$8#
B3	List	='Data Source'!$E$8#

 

[Gooby]

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this could work for you set up two formulas like I have shown in D8:E8 below (values in E8 & below will only appear once Client has been chosen in B2 on the PSF sheet). These columns could be hidden and/or could be on PSF Index Summary or even on a separate worksheet.

Gooby.xlsm
	A	B	C	D	E
7	Client	Proj ID		Client	Proj ID
8	Client 1	1		Client 1	4
9	Client 2	4		Client 2	2
10	Client 3	5		Client 3
11	Client 2	2
12	Client 3	3
13
Data Source

Cell Formulas
Range	Formula
D8:D10	D8	=UNIQUE(FILTER(A8:A100,A8:A100<>""))
E8:E9	E8	=UNIQUE(FILTER(B8:B100,(B8:B100<>"")*(A8:A100='PSF Index Summary'!B2),""))
Dynamic array formulas.

Then the Data validation lists could be set up like this

Gooby.xlsm
	A	B
1
2	Client	Client 2
3	Proj ID
4
PSF Index Summary

Cells with Data Validation
Cell	Allow	Criteria
B2	List	='Data Source'!$D$8#
B3	List	='Data Source'!$E$8#

View attachment 95775

Thank you very much! This has worked perfectly. I will keep the XL2BB feature in mind for future queries

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.