Formula Order of Operations Problem

Michelle Rogers · 03/09/25

I am trying to use a formula with CHAR(10) between each missing asset item to build a nice list to use in a record search return automation. I am using one row for each employee with multiple uniform items in several columns. My formula does well until it finds an item in the formula sequence is not missing, then it quits

I need the formula to return all missing items in the row sequence in a tidy list to be used for my automation. The result needs to be in the "Final Asset Assessment" column.

Thank you in advance!

Here is my formula with a screenshot of my tracker:

=IF([Missing Asset]@row = 1, (IF([Class C Pant Issuance]@row = "", "", [Class C Pant Issuance]@row + CHAR(10) + IF([Class C Shirt Issuance]@row = "", "", [Class C Shirt Issuance]@row + CHAR(10) + IF([Class C Belt Issuance]@row = "", "", [Class C Belt Issuance]@row + CHAR(10) + IF([Class B Pant Issuance]@row = "", "", [Class B Pant Issuance]@row + CHAR(10) + IF([Class B Shirt L/S Issuance]@row = "", "", [Class B Shirt L/S Issuance]@row + CHAR(10) + IF([Class B Shirt S/S Issuance]@row = "", "", [Class B Shirt S/S Issuance]@row + CHAR(10) + IF([Class B Belt Issuance]@row = "", "", [Class B Belt Issuance]@row + CHAR(10) + IF([Jacket Issuance]@row = "", "", [Jacket Issuance]@row + CHAR(10)))))))))), "")

Michelle Rogers · 03/09/25

Smartsheet AI Generated a successful formula… Thank you to Smartsheet AI… I will leave the post in case it would be helpful to others!

=JOIN(COLLECT([Class C Pant Issuance]@row:[Jacket Issuance]@row, [Class C Pant Issuance]@row:[Jacket Issuance]@row, NOT(ISBLANK(@cell ))), CHAR(10))

Michelle Rogers · 03/09/25

A new formula attempt is promising if I can remove the gaps in the list for missing items..

=JOIN([Class C Pant Issuance]@row + CHAR(10) + [Class C Shirt Issuance]@row + CHAR(10) + [Class C Belt Issuance]@row + CHAR(10) + [Class B Pant Issuance]@row + CHAR(10) + [Class B Shirt L/S Issuance]@row + CHAR(10) + [Class B Shirt S/S Issuance]@row + CHAR(10) + [Class B Belt Issuance]@row + CHAR(10) + [Jacket Issuance]@row)