How to write Nested IF statements each on separate line?

Waseem K

Hi, 

I have this nested IF statement that I would like to have each IF condition on a new line for easier reading and to follow coding best practices. 

Here is what I have now: 

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))), IF([% Complete]@row = 1, "Gray", IF(Finish@row >= TODAY() + 7, "Green", IF(Finish@row >= TODAY() + 3, "Yellow", "Red"))))

 

I would like it to look like this: 

=IF(COUNT(CHILDREN([Task Name]@row)) > 0,

  IF(COUNTIF(CHILDREN(), "Red") > 0, "Red",

  IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow",

  IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", "Gray"))),

  IF([% Complete]@row = 1, "Gray",

  IF(Finish@row >= TODAY() + 7, "Green", IF(Finish@row >= TODAY() + 3, "Yellow", "Red"))))

 

I would appreciate if anyone can guide me to the shortcut or a way to get this done, thanks!

Mike Wilday

I'm able to accomplish this by using a plain-text editor. I've used Notepad++ for windows and Atom for Mac. Copying and pasting the line by line from the text editor to Smartsheet works for me and then I can save/archive those formulas for future editing if needed. 

Waseem K

Thanks mike. 

This worked for me when I paste it the multi-line code, but once i press enter and go back to edit the formula in the cell it goes back to one line and does not save the way I paste it! 

I Don't wish to save the formula. I'm wondering if Smartsheet have that functionality built-in already?

Thanks again for your response and I look forward to finding out if this can be done from within the cell! 

Mike Wilday

There is no functional way to do what you want to do in SmartSheet. Sorry.

 

Andrée Starå

Hi Waseem,

Unfortunately, it's not possible at the moment, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting