Employee Main List Formula Modification

StephanieS

I am currently using the Return to Workplace template set. I want to modify the formula being used on the Employee Main List Sheet to only collect trainings attended if the column next to it says a specific project phase (Dropdown). I am dividing the trainings by phases but I don't want the Trainings Attended to account for trainings for all phases since the "Attended Appropriate Training" formula would always show as "No".

The formula already implemented into the sheet is this one:

=IF(ISBLANK(JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row)), CHAR(10))), "No Trainings Attended", JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row)), CHAR(10)))

Paul Newcome

You would add another range/criteria set inside each of the COLLECT functions.

=IF(ISBLANK(JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row, {Range}, Criteria)), CHAR(10))), "No Trainings Attended", JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row, {Range}, Criteria)), CHAR(10)))

StephanieS

@Paul Newcome I have tried implementing this but it says "Incorrect Argument Set". This is what I entered =IF(ISBLANK(JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row, {Attendance Phase}, Phase@row)), CHAR(10))), "No Trainings Attended", JOIN(DISTINCT(COLLECT({EmployeeTrainings}, {EmployeeEmail}, [Employee Email]@row, {Attendance Phase}, Phase@row)), CHAR(10)))

The "Attendance Phase" is the column in the Employee Attendance Sheet and the "Phase @row" is the cell in the Employee Main List sheet that is needed to match.

Paul Newcome

Double check your ranges. Each range should only be a single column.

StephanieS

@Paul Newcome I have found the problem. The Attendance Phase column that I am including in the formula, cross referencing from the other sheet, is not covering the whole column range. The issue is that I am selecting the whole column, but it only include the rows that already have information inputted. I tried several times, and it is still doing the same thing.

Paul Newcome

Are you clicking on the column header to select the range?

StephanieS

That worked, thank you! @Paul Newcome

Paul Newcome

Happy to help. 👍️