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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!