Creating a list of projects based on who worked on it

Copper Contributor

Hi everyone,

I have a worksheet similar to the below only WAY bigger in each direction.


What I would like to try to accomplish if it's possible is to create a dropdown list of all the "Company Ids" which when an ID is selected would then, in another area, produce a list of all the "Projects" that the ID is associated with.

For example, If I selected ID-09, The list would state Projects 1, 7, 9 and 11 were assoc to the ID.

The problem that I have found is that all the lookups and other ways never seem to be able to give the top level data.


Thanks in advance for any help you may be able to give.



2 Replies


Let's say your worksheet is named Project Sheet.

On another sheet, you create a drop-down list in A1 that lists all company IDs.

In B1, enter the formula


=FILTER('Project Sheet'!$B$1:$ZZ$1, INDEX('Project Sheet'!$B$2:$ZZ$1000, MATCH(A1, 'Project Sheet'!$A$2:$A$1000, 0), 0)="Y", "")

That's amazing, Thank you very much for that. I really appreciate it.