Multi Select

ScottPinson_AlliedSolutions

I have a multi select drowndown with users. (for example: Bill, Joe, Ted, John, and Tom) are selected. I am trying to look in another sheet to tell me if these people have submitted a status for project 123. (Sheet 2 is a list of status's submitted:

Project | Submitted by | Status

123 | Bill | Green

123 | Joe | Green

123 | John | Green

So I want to populate who is not in the table: ie: Ted, Tom

KPH

Hi @ScottPinson_AlliedSolutions

You can use a COUNTIFS function to count the number of rows where the Project is 123 and the Submitter is "Bill".

Put that into an IF function so that if the count is 0, the IF returns "Bill".

Concatenate that with a CHAR(10) which is a line break (and the separator in a multi select drop down).

Then create another IF COUNTIFS for Joe.

Another CHAR(10).

Repeat for Ted, etc.

If the data looks like this:

Your formula would look like this:

=IF(COUNTIFS(Project:Project, ID@row , {Submitted by}, "Bill") = 0, "Bill") + CHAR(10) + IF(COUNTIFS(Project:Project, ID@row , {Submitted by}, "Joe") = 0, "Joe") + CHAR(10) + IF(COUNTIFS(Project:Project, ID@row , {Submitted by}, "Ted") = 0, "Ted") + CHAR(10) + IF(COUNTIFS(Project:Project, ID@row , {Submitted by}, "Tom") = 0, "Tom") + CHAR(10) + IF(COUNTIFS(Project:Project, ID@row , {Submitted by}, "John") = 0, "John")

Where {Submitted by} is the cross sheet reference to the submitted by column in sheet 2.

I made the ID a column that is referenced in the formula. If you want it to be fixed, just replace ID@row in the formula with "123".

Hope this helps.