Using IF and unchange the cell value if it happens to be false.
![topazfae](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I have this worksheet
Column B is a drop list of variables - "Completed", "In Progress", "Enrolled", etc.
Column C is Date Completed (meaning if the learner completed the course, we stamped the date when this learner completed the course.
So when I input in the date, I would like. Column B to change to Completed form whatever status it is.
I tried this formula -
=IF(NOT(ISBLANK([Date Completed]@row)), "Completed", Status@row)
But I got the error "Circular Reference" which means sense because I tried to refer back to the original cell.
Is there a way for me to ignore any other status and change the status if the date is presented?
Best Answer
-
Hi @topazfae,
Rather than doing this with a formula, you can use workflow automation for this, similar to the below:
This will do the update if a date is added. If necessary, you can also do a similar workflow where if the status is changed to complete, a date is added to the "Date Completed" column.
Hope this helps, but if you've any questions then just ask! 🙂
Answers
-
Hi @topazfae,
Rather than doing this with a formula, you can use workflow automation for this, similar to the below:
This will do the update if a date is added. If necessary, you can also do a similar workflow where if the status is changed to complete, a date is added to the "Date Completed" column.
Hope this helps, but if you've any questions then just ask! 🙂
-
Hi topazfae,
Please try the below formula-
=IF(NOT(ISBLANK([Date Completed]@row)), "Completed", "")
Thank you,
Shubham Umale
Associate - Smartsheet, Ignatiuz Software
-
@Nick Korna I like the idea of workflow - but when I did that, it changed all rows into completed (yikes), so I have to check my data and revise it back where it was.
So there is something missing from the workflow. What I have is -
›
Not sure what I did wrong. I am checking the Date Completed to make sure they are blank for those who have not completed.
-
@Shubham Your answer works but it will leave a blank cell which is not what I wanted. For example, in your example, you see In Progress - if you autofill down, it will make In Progress to disappear.
The workaround I did was adding a hidden column to accomplish what I need but for now, I will do them manually until I find a solution.
Thanks!
-
Hi topazfae,
You are right and I can understand your concern. Can you please let me know if you have any date column that captures the ‘Enrolled date’ and ‘In Progress’ date?
If you have, then we can modify the condition accordingly and it would be great if you also let me know the priority of the status too.
Thank You!
Shubham Umale
Associate - Smartsheet, Ignatiuz Software
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!