Join, Collect functions

Josephine

Hi All

I am trying to retrieve results from several columns on a seperate sheet into 1 cell on another sheet. This formula works -

=IF(INDEX({SUS Badge}, MATCH([Full Name]@row, {SM Name}, 0)) = "Not achieved", "SUS Core", " ")

But as soon as I add more columns -

=JOIN(
IF(INDEX({SUS Badge}, MATCH([Full Name]@row, {SM Name}, 0)) = "Not achieved", "SUS Core", ""),
IF(INDEX({HSE 1}, MATCH([Full Name]@row, {SM Name}, 0)) = "Not achieved", "HSE1", ""),
", ")

I get an error. I potentially want to add in a total of 13 columns where in each column it is looking for Not Achieved and then returning the name of the column

Each column is a unit standard for internal training and I am wanting to have in one place which unit standards still need to be completed for the badge.

Above is the source sheet, imagine there is employee names off to the side. The destination sheet also has name column and then a column called Site Manager Certified and in the one cell I want to list all the Not achieved competencies.

Josephine

OK I figured it out. In the source sheet I have added a hidden column for the following -

=IF([Sus badge]@row = 0, "Sus Badge, ", "") + IF([HSE 1]@row = 0, "HSE 1, ", "") + IF([HSE 2]@row = 0, "HSE 2, ", "") + IF([OPS core]@row = 0, "OPS Core, ", "") etc etc

Then in the destination sheet used =INDEX(COLLECT

Josephine

OK I figured it out. In the source sheet I have added a hidden column for the following -

=IF([Sus badge]@row = 0, "Sus Badge, ", "") + IF([HSE 1]@row = 0, "HSE 1, ", "") + IF([HSE 2]@row = 0, "HSE 2, ", "") + IF([OPS core]@row = 0, "OPS Core, ", "") etc etc

Then in the destination sheet used =INDEX(COLLECT