How does this JOIN/COLLECT formula work in one sheet but not another, mirrored sheet?

WtaylorW ✭✭
edited 01/09/24 in Formulas and Functions

This sheet originally had a formula that would COLLECT and JOIN the column names of every value in a row that was "Fail". It also separates the collected column names by ", " (comma and space). A coworker has changed the formula, and it still works, but only on this sheet. Now I can't remember my original formula, and I've never seen what they have with the "Fail" value being inside the column name brackets. Does anyone have any insight how this works, why only on this sheet, and how I can get the results I need with a similar formula that uses a range and doesn't list each column individually?

The formula:

=JOIN(COLLECT([Lights Fail]@row:[Steering Test Fail]@row, [Lights Fail]@row:[Steering Test Fail]@row, <>""), ", ")

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!