Smartsheet Calendar unable to update health status on source sheet

Hi, I created a Smartsheet calendar organized by Phase and then Workstream, with deliverable bars spanning duration. The goal is to create an interactive Gantt chart, which team members can update tasks on the calendar detailed side panel.

The formula on the source sheet states that if At Risk is checked then Health is Yellow. However, if this At Risk is updated on the calendar, it is unable to change the Health Status on the Calendar and source sheet and ends up deleting the health formula on the source sheet corresponding Health cell.

Any ideas on how to fix this?

Find more posts tagged with

Sheets

Calendar View

Formulas

Automations

Calendar App

Accepted answers

All comments

Cayla Davis

Hi Eric - I'm trying to visualize what you are doing, i believe you created a Project sheet - this is how i setup mine when i was doing something very similar

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", "Red", IF(Status@row = "In Progress", "Green", IF(Status@row = "Risk", "Yellow"))))

Do you have a screenshot or a mockup of the calendar?

Eric P

Hi Cayla, Thanks for reaching out to help. The formulas on the source sheet all work great. The problem is if I update the Risk directly on the Calendar detailed side panel, the risk status updates, but the Health is unable to change according to the formula, and in fact when attempting to do so, actually deletes the formula in that cell.

If I change the Health formula to a Column formula, then niether the Risk status or Health is able to respond. The problem seems to be with the Calendar function interacting with the sheet.

Eric P

Source Sheet View:

Calendar View:

Adding a risk to a task:

Outcome: Risk updated but Health unchanged.

Source sheet risk updated but health unchanged:

Source Sheet Formula override by Calendar now deleted:

Eric P

Source Sheet View:

Calendar View:

Adding a risk to a task:

Outcome: Risk updated but Health unchanged.

Source sheet risk updated but health unchanged:

Source Sheet Formula override by Calendar now deleted:

Eric P

So it seems the Calendar detailed side panel overrides and deletes the Health formula when updating the Risk, but it is able to update risk, just breaks down when attempting to update the health via formula.

And, If I set the Health to a column formula, then calendar is no longer able to update at all, even the risk.

Andrée Starå

https://community.smartsheet.com/discussion/142072/smartsheet-calendar-unable-to-update-health-status-on-source-sheet

Hi,

I hope you're well and safe!

Strange!

If you haven’t already, I recommend submitting a support ticket through the new Smartsheet Support Portal.

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

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.