Error with Convert to column formula

Hi,

I have a formula that works for each cell in a column when I copy and paste it. However, when I try to "convert column to a formula" I get an error message saying my syntax is incorrect.

=IF([Start Date]2 > TODAY(), "Blue", IF(AND([% Complete]2 < 1, [End Date]36 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green")))

if I copy it to row 3 it works

=IF([Start Date]3 > TODAY(), "Blue", IF(AND([% Complete]3 < 1, [End Date]36 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green")))

But I want the logic to apply to the entire column so I don't have to copy and paste the formula each time.

Any help is appreciated!

Best Answer

  • Rushikesh Parab
    Answer ✓

    Hi Melissa,


    Please replace the no. of row in formula with "@row" and then try to covert it to column formula.

    For Example :-

    IF([Start Date]@row > TODAY(), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green")))


    Please let me know if this works for you.


    Thanks.

Answers

  • Rushikesh Parab
    Answer ✓

    Hi Melissa,


    Please replace the no. of row in formula with "@row" and then try to covert it to column formula.

    For Example :-

    IF([Start Date]@row > TODAY(), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green")))


    Please let me know if this works for you.


    Thanks.

  • Fantastic! That works! I'm new to SmartSheets so this is very helpful!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!