Using IF and unchange the cell value if it happens to be false.

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?

Find more posts tagged with

Formulas

Accepted answers

Nick Korna

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! 🙂

All comments

Nick Korna

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! 🙂

Shubham

Hi topazfae,

Please try the below formula-

=IF(NOT(ISBLANK([Date Completed]@row)), "Completed", "")

Thank you,

Shubham Umale

Associate - Smartsheet, Ignatiuz Software

topazfae

@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 -

›

Screenshot 2023-09-06 at 11.24.01 AM.png

Not sure what I did wrong. I am checking the Date Completed to make sure they are blank for those who have not completed.

topazfae

@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!

Shubham

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.