Is there a way to query and reference a cell that will change?

Question

Alright, looking for some help. I think I am close to the solution, but I could use some help getting it over the finish line. This spreadsheet link is a replicated document of my situation.

GoogleSheet link here

Problem: I am using this formula/query below to get the names of all "team members" associated with a project into one cell (see the Summary portion of the sheet)

=arrayformula(textjoin(" ",TRUE,query($B$5:$C$21,"Select B where C = 'Project A'")))

You can see that this is currently working pretty well. The problem is the hardcoded "PROJECT A, B, C, etc." in the formula. Column F is a query that pulls all the unique projects from Table 1.

So when Column F values change due to Column B values changing, my formula above does not automatically adjust for the new value in the cell next to it (I have to go in and manually update "Project A" to whatever the new project name is.

Is there any way around this manual update?

Answer 1

Use string concatenation, like this:

=textjoin( " ", true, query(B$5:C, "select B where C = '" & F5 & "' ", 0) )