Heath Calculation based on Critical Path Health

Hello!

I'm wanting to calculate the health of my project based on the health of any task that is on the critical path.

I have seen the blog post about being able to create a filter that is able to show the critical patch, but is there a way to derive via a formula if the task is on the critical path or not? I would probably use a checkbox column and have a simple formula such as IF[Task]@row = "Critical Path", "True", "False")... Then I could integrate this into my health formulas.

I'm assuming no since I've not been able to find a lick of documentation with this mentioned... Figured I would poll the community.

Thanks in advance!

Zach

Find more posts tagged with

Sheets

Conditional Formatting

Gantt View

Project Management

Comments

Andrée Starå

Hi Zach,

Unfortunately, as far as I know, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

PhillyPepboy

Hi Zach,

You can use this formula for checking the health of a TASK in the SHEET. You will have to incorporate this with your Critical Path Logic to get what you want. This is just a RAG status for the task. Please Create a NEW Column called "RAG" and enter this formula in CELL RAG1 , then copy drag down.

=IF([Task Name]1 <> "",

IF([% Complete]1 = 1, "Complete",

IF(AND(Finish1 >= TODAY(), [% Complete]1 = 0), "Not Started",

IF(AND(Finish1 < TODAY() + 7, [% Complete]1 < 1, [% Complete]1 >= 0.8), "Yellow",

IF(AND(Finish1 < TODAY() + 7, [% Complete]1 < 0.8), "Red", IF(AND(Finish1 > TODAY() + 60, [% Complete]1 > 0), "Green",

IF(AND(Finish1 > TODAY(), Start1 > TODAY(), [% Complete]1 > 0), "Green",

IF(AND(Finish1 > TODAY(), Start1 > TODAY(), [% Complete]1 = 0), "NotStarted", "Green"))))))), "")

Conditional Formatting for RAG.png

Paul Newcome

Andree is correct. There is no way to directly reference the Critical path via a specific reference that could be included in a formula or function.

I reached out to SS about this very thing some time back. The two options you have are to either create some formula that spells out the critical path or manually mark each row that is on it.

That particular project unfortunately got put on the back burner for me, so I haven't been able to work on writing something out to automatically check a box based on critical path criteria yet.

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…