Reference two columns to populate a third
Hi,
I'm fairly new to Smartsheet and am stuck trying to figure out a formula. I have three columns; 'Due Date', 'Complete' and 'Status'. Due is a XX/XX/XX format, Complete is a drop down with 'Yes or 'No' and Status is blank. Here's what I need:
If 'Due Date' is past the current date (today) and Complete = No, then Status needs to populate with 'PAST DUE'
If 'Due Date' equals tomorrow and Complete = No, then Status needs to populate with 'Due Tomorrow'
If 'Due Date' equals 2 or more days from today and Complete = No, then Status needs to populate with 'In Progress'
If 'Due Date' equals any date and Complete = Yes, the Status needs to populate with 'COMPLETED'
Any help is greatly appreciated! I've been struggling with this formula for a couple of days now. Maybe I need more than one formula? I'm not sure.
Thanks!
Best Answer
-
Just a missing comma immediately before "Due Tomorrow".
I also suggest using "@row" instead of row numbers if you are referencing cells on the same row as the formula.
Answers
-
Try something like this...
=IF(Complete@row = "Yes", "COMPLETED", IF(TODAY() >= [Due Date]@row, "PAST DUE", IF(TODAY(1) = [Due Date]@row, "Due Tomorrow", "In Progress")))
-
Hi Paul,
Thanks for posting an option. I was way off, I thought I was need and 'IF(And' formula. I'm still getting #UNPARSEABLE, I did:
=IF(Complete1="Yes", "COMPLETED", IF(TODAY()>=[Due Date]1,"PAST DUE", IF(TODAY(1)=[Due Date]1"Due Tomorrow","In Progress")))
Any thoughts on what is causing the error?
-
Just a missing comma immediately before "Due Tomorrow".
I also suggest using "@row" instead of row numbers if you are referencing cells on the same row as the formula.
-
Hi Paul,
I can't believe I missed that comma. Thanks so much! I used your other suggestions as well.
Thanks again!
-
No worries. Happy to help! 👍️
Please don't forget to mark an "Accepted Answer" so that others know a solution has been found.
-
Hi again!
I just found that in addition to the options above that you helped me with, Complete may also have 'Cancelled'. If I want Status to show 'CANCELLED' for those cells, can I just add =IF(Complete@row = "Cancelled", "CANCELED" to my formula?
Thank you!
-
Yes. It would look something like this:
=IF(Complete@row = "Yes", "COMPLETED", IF(Complete@row = "Cancelled", "CANCELLED", IF(TODAY() >= [Due Date]@row, "PAST DUE", IF(TODAY(1) = [Due Date]@row, "Due Tomorrow", "In Progress"))))
Notes:
I placed it near the beginning of the formula so that it would be run immediately after the formula finds that it is not "Yes". This means we still do not have to specify that Complete@row = "No" for the rest of them. If you have three option in that column and the first two have already been checked as false, then by default it MUST be the third option which means we can save the hassle of having to specify it.
There is also one more closing parenthesis at the end of the formula since we have added one more IF statement.
-
Perfect! Thank you!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!