How to concatenate multiple IF statements' texts

aprilwu

Hello!

I'm new to Smartsheet. I have a sheet that used to be in an Excel file. There's a column call "Currently Delay (Rolled Up Reasons)" which I'm having trouble creating. The original Excel formula is:

=IF(AU6="Yes", CONCATENATE(

IF(AND(AE6<>"", AF6 > TODAY()), $AE$3 & " ","")

, IF(AND(AG6<>"", AH6 > TODAY()) ,$AG$3 & " ","")

, IF(AND(AI6<>"", AJ6 > TODAY()), $AI$3 & " ","")

, IF(AND(AK6<>"", AL6 > TODAY()), $AK$3 & " ","")

,IF(AND(AM6<>"", AN6 > TODAY()) ,$AM$3 & " ","")

,IF(AND(AO6<>"", AP6 = "Incomplete"), $AO$3 & " ","")

, IF(AND(AQ6<>"", AR6 > TODAY()), $AQ$3 & " ","")

,IF(AND(AS6<>"", AT6 > TODAY()), $AS$3 & " ","")),"")

-- AU: Currently Delay (Yes/No)

-- AE: Commitment Start / AF: Commitment End / AE3: Commitment

-- AG: Site Selection/LOI Start / AH: Site Selection/LOI End / AG3: Site Selection/LOI

-- AI: Leasing Start / AJ: Leasing End / AI3: Leasing

-- AK: Drawings Start / AL: Drawings End / AL3: Drawings

-- AM: Permitting Start / AN: Permitting End / AM3: Permitting

-- AO: Equipment Start / AP: Equipment End / AO3: Equipment

-- AQ: Construction Start / AR: Construction End / AQ3: Construction

-- AS: Inspections Start / AT: Inspections End / AS3: Inspections

I'm trying to replicate this formula in Smartsheet, but I haven't been successful. I've tried couple different ways already. So I'm hoping someone here can help. Thank you in advance!

Andrée Starå

Hi @aprilwu

I hope you're well and safe!

At a glance, it looks like you should look into using JOIN and COLLECT.

https://help.smartsheet.com/function/join

https://help.smartsheet.com/function/collect

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!