Need Assistance; IF, JOIN, DISTINCT, COLLECT.....clueless.

Kris V. Le

Ok....so please bear with me.

Here is what I am looking to do. I am building a training management system that will essentially comprise 3 different sheets; a training matrix, a training log, and a training status tracker.

The issue I am having is with the status tracker, see below:

I am trying to pull in training tasks into the "Tasks Assigned" from my training matrix sheet based on certain criteria (see below). I will have multiple training modules and a status tracker (above) for each module. I'd like to have a [Tasks Assigned] column formula that Collects and Joins Distinct child training tasks when they are under a specific parent training module. [Tasks Completed] is Collecting and Joining Distinct training tasks that are completed when logged under a specific employee email, which I'm not having any issues with currently using this formula =JOIN(DISTINCT(COLLECT({Employee Trainings}, {Employee Email}, [Employee Email]@row)), CHAR(10)). (If I'm being honest I do not fully understand the "CHAR(10)" functionality to this formula, I copied this formula from a template so any clarity would be helpful).

[Completion Status] reconciles the tasks completed which are retrieved from the log for a specific employee against the tasks assigned and returns the YES or NO value.

I guess the issue I'm having is combing some of the IF and Parent/Child logic to the Join Collect function.

Please help and thanks in advance!

Jeff M.

The CHAR(10) appears to be a line break between each returned item. This is an option with the JOIN function so that you can add spaces, commas etc.. when joining multiple items. If you select the text wrap option from the top formatting tools, you will see that each item will drop to a new line.

JOIN( range [ delimiter ]) delimiter —[optional] The value to separate the values in the range (e.g., a hyphen, comma, or space). If no delimiter is provided, the values from the range will be joined without spaces (e.g., "Task ATask BTask C")

I'm not 100% clear on what you are attempting to use the IF statement to collect? Could you give an example for what you want to collect & based on what condition?