Employee Main List Formula Modification
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)))
Answers
-
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)))
-
@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.
-
Double check your ranges. Each range should only be a single column.
-
@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.
-
Are you clicking on the column header to select the range?
-
That worked, thank you! @Paul Newcome
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!