How do you exclude a specific dropdown status from another sheet in a formula?

Di Mueller

I have 2 sheets. One sheet (Requestors) is tied to a form for people to get information about a possible coach. Through this awesome community, I've been able to figure out how to pull the information I need from the 2nd sheet (Coaches list), and display it in an automated email to the requestor (with a line break between each possible name!!).

However, we now have some coaches that are currently unavailable but will be in the future. I need to figure out how to exclude those coaches information from being pulled and sent to the requestors. I have 2 different availability statuses, tied into other formulas and conditional formatting and automations . One is if a coach is active but has reached their limit of requestors to take on (Flag). The other is if they are currently able to participate in the program (Status with Green, yellow, red).

I'd like to base it off the Flag status, because that will end up being used for both unavailable statuses.

What do I need to add to my =JOIN(COLLECT({Coaches list for Cross-Enterprise Coach name}, {Coaches list for Cross-Enterprise Topics}, HAS(@cell, [What type of coaching help are you looking for?]@row)), " or ") to only pull coaches that have a clear flag status?

Thanks!

KPH

Hi @Di Mueller

You can add a second criteria to the COLLECT part of your formula. So the rows collected will be those where the topic matches the "What type of coach" and the status matches green.

=JOIN(COLLECT({Coaches list for Cross-Enterprise Coach name}, {Coaches list for Cross-Enterprise Topics}, HAS(@cell, [What type of coaching help are you looking for?]@row) This is where the new part goes - before the closing parenthesis for the COLLECT ), " or ") 

The syntax is comma, criterion range, comma, criterion.

It would look something like this (depending on the names you give your cross sheet references).

=JOIN(COLLECT({Coaches list for Cross-Enterprise Coach name}, {Coaches list for Cross-Enterprise Topics}, HAS(@cell, [What type of coaching help are you looking for?]@row), {Coaches list for Cross-Flag Status}, "Green" ), " or ") 

Di Mueller

Hi @KPH it did work correctly, for the most part but now the data from the multiple cells it used to populate from the 2nd sheet doesn't appear in the cell and I'm not sure why. It usually pulls the names of the coaches that have matching topics to the requestor sheet, but now it is blank.

KPH

I'm sorry @Di Mueller I don't understand. Can you share a screen shot showing what you see and what you want to see?

Di Mueller

Hi @KPH

So this is what will usually populate in the "Coach Possibilities" column without making the changes you suggest from the =JOIN(COLLECT({Coaches list for Cross-Enterprise Coach name}, {Coaches list for Cross-Enterprise Topics}, HAS(@cell, [What type of coaching help are you looking for?]@row)), " or ") formula.

In adding the , {Coaches list for Cross-Flag Status}, "Green" , the names no longer show.

Is there a way to still have the names pull through with the formula?

Di Mueller

I figured it out. Flags are 1 or 0. Not color

KPH

Great. I'm pleased that works for you.