Project Status Formula Help

Laurenzella · 04/01/25

I have a formula I need help with for the top level of my project checklist hierarchy.

Overall Project Status - this is looking only at the highest level rows which are indicated by the helper column Hierarchy=0.
1. The objective of the formula is as follows:
  1. If the Project Override field (in the Sheet Summary) is filled out = Display that value
  2. If the Project Override field is blank - then look at all parent rows where the following is true:
    1. "Default Filter" column is checked AND
    2. "Summary" column is not checked AND
    3. "Hierarchy" column = 0 THEN
    4. Look at "Status" column
      1. If all the rows say "Completed" > Project Status: Complete
      2. If all rows say "Not Started" > Project Status: Planning
      3. If any row says "In Progress" > Project Status: Active

This is the current formula we have that does not appear to be working:

Formula =IF([Project Status Override]# <> "", [Project Status Override]#, IF(AND(COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, OR(@cell="Completed", @cell="Cancelled")) = COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Complete", IF(AND(COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0, Status:Status, "Not Started") = COUNTIFS([Tasks Needed]:[Tasks Needed], 0, Summary:Summary, 0, Hierarchy:Hierarchy, >0)), "Planning", "Active")))

Paul Newcome · 04/04/25

Right. Ok. So I would put all the extra stuff on another sheet (or just let users know where how to find it in the Control Center sheet).

Then I would move the two piece for the filter into sheet summary fields. This will allow you to completely get rid of the entire top section from that sheet. This will also allow for much simpler formulas and negate a lot of the risk of circular references.

Paul Newcome · 04/04/25

Not directly. You would have it linked into another sheet (most likely the one that has the rest of the data). Then you can write a basic formula with a cross sheet reference in the Summary field that pulls in just that one cell.

=INDEX({Reference to single cell}, 1)

Paul Newcome · 04/01/25

Give this a try:

=IF([Project Override]# <> "", [Project Override]#, IF(COUNT(DISTINCT(COLLECT(Status:Status, [Default Filter]:[Default Filter], @cell = 1, Summary:Summary, @cell <> 1, Hierarchy:Hierarchy, @cell = 0))) = 1, INDEX(COLLECT(Status:Status, [Default Filter]:[Default Filter], @cell = 1, Summary:Summary, @cell <> 1, Hierarchy:Hierarchy, @cell = 0), 1), "In Progress"))

Double check field / column names are correct. I noticed there are some differences between your description and the actual formula.

Laurenzella · 04/01/25

You're right- "Project Override" is actually "Project Status Override" but when I tried that it gave me a Circular Reference. I double-checked all of the other column names.

Paul Newcome · 04/01/25

Where exactly are you putting this formula?

Laurenzella · 04/01/25

Please see screenshots below where I have marked off each of the columns that is used in the formula. thank you!

Status Column & Project Status Override

Default Filter

Summary & Hierarchy

Paul Newcome · 04/02/25

What are the formulas you have in each of those referenced columns?

Laurenzella · 04/02/25

In the Default Filter column:

=IF([Summary Metric / Nav]@row = 1, 1, IF([Summary Blank]@row = 1, 1, IF([Sub-Type Needed]@row = 1, 1, IF([Tasks Needed]@row = 1, 1, 0))))

In the Hierarchy column:

=COUNT(ANCESTORS())

Paul Newcome · 04/02/25

What about formulas in cells being referenced by the [Default Filter] column?

What is the reason for this section? Is it possible to move it to a separate sheet?

Laurenzella · 04/02/25

Our solution was built by Smartsheet but some things have changed and the formulas are not working anymore - the default filter column is a helper column - when a new project is submitted via an intake form, this checklist is created and depending on what items have been picked in Project Type / Subtype (profile data) the applicable rows will be checked and appear when the Default filter is applied (which it always is by default). I don't think we could move this to a separate sheet because it determines the parameters of the filter.

The formulas in the columns it references are as follows:

Summary / Metric Nav - No formula, checkbox column

Summary Blank - =IF([Summary Metric / Nav]@row = 1, 0, IF(AND(ISBLANK(Notes@row), Summary@row = 1), 0, IF(AND(Notes@row <> "", Summary@row = 1), 1)))

- I believe this one is saying that if any of the profile data fields are blank (because they were not filled in the intake sheet) then they shouldn't be displayed at the top of the checklist.

Sub-Type Needed: =IF(CONTAINS([Sub-Type Template]@row, [Project Sub-Type]@row), 1, 0)

Tasks Needed: =IF(COUNTIF([Sub-Type Needed]:[Sub-Type Needed], 1) > 0, 0, IF(CONTAINS(Template@row, [Project Type]@row), 1, 0))

Paul Newcome · 04/02/25

It honestly seems as if it was built to be way more complex than it really needed to be. This whole top section is determining filter parameters?

Laurenzella · 04/02/25

It is definitely too complex for me :). The filter parameters determine which checklist rows show up - so if I submit a project it is Project Type: marketing and Marketing Subtype: Social Media,, then the rows that match Marketing / Social Media in the checklist are going to display when the Default filter is applied. I'm including some screenshots below in case that helps illuminate anything:

Paul Newcome · 04/03/25

So it is just the project type and subtype that drive the filter. What about the rest of that top section? The part I assume is being checked as "Summary"?

Laurenzella · 04/04/25

The Summary info is being pulled in via cell link from a Profile Data sheet that is created in control center when a new project is provisioned. Only the Summary items that contain content are displayed on the checklist (the purpose of the Summary Blank helper column) and I assume the Summary checkbox column itself is to keep those items out of the various formulas and reports that are based on the status of the checklist items. Does that help at all?

Paul Newcome · 04/04/25

Do those things need to actually be in that particular sheet?

Laurenzella · 04/04/25

I suppose it could be in another sheet generated in the same project folder - but we would still have to bring in at least the Project Type and Subtype cell links from the profile data because that's what drives the formula for which tasks display.

Paul Newcome · 04/04/25

Right. Ok. So I would put all the extra stuff on another sheet (or just let users know where how to find it in the Control Center sheet).

Then I would move the two piece for the filter into sheet summary fields. This will allow you to completely get rid of the entire top section from that sheet. This will also allow for much simpler formulas and negate a lot of the risk of circular references.

Formulas and Functions

Formulas and Functions

Project Status Formula Help

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Is there a way to make a field in a form auto populate street address as its being entered?

IF formula for INDEX/MATCH across sheets

NETDAYS Formula Question