Formula(s) to select & show project status

Teanna Williams

For each project sheet, I show the total number of tasks over due (red), due within 7 days (yellow) and not yet due (green).

Now I want to create an executive report that shows a red, yellow, or green light for each sheet based on the category that has the highest number of tasks.

Ultimately, the executives will receive a report that lists each project name with the appropriate indicator (red, yellow or green) next to it.

 

Can anyone help me accomplish this?

 

Thank you,

Teanna

Christian Wells

Hi Teanna,

 

I think you will need to develop the summary on the project sheet to give you a status for that project.  Perhaps make this on Row 1, perhaps make it the parent of all other rows in the project sheet to help you woth some formula's.....

I recomment naming this something specific like 'Status' or 'Summary'

 

Then Create a report across your multiple project sheets, For the filtering, select the name column of 'Status' or 'Summary', display the sheet name (as project name) and display the summary data you wish to display to the executives.

 

Hope this helps

Teanna Williams

Hi Christian, thank you for assisting me!  The screen shot below shows the summary I have on each project sheet.  I think you are saying to have a column on each project sheet that shows the overall status for that sheet.  Is that correct?

 

If that is correct, then I guess I need to add an overall status column that selects the highest of the red, yellow, green tasks.  I need help with that formula.  I'm going to research a max/min type formula then somehow I need to make the correct color display.  Any assistance you can provide with these formulas would be greatly appreciated. 

 

Also, if you can think of a simpler way for me to accomplish this goal, I'm open to it.  I may be over thinking it???

 

Thanks again,

Teanna

 

J. Craig Williams

Teanna,

 

What I think you want is to determine the RYG ball with the most number of instances.

John Hammond and John Sauber's posts in this thread:

 

https://community.smartsheet.com/discussion/ryg-balls-changing-children-rows

 

should get you there.

(follow the original thread, not the follow on tangent)

 

To give you an exact formula, I would want to know things like what happens when the colors are equal.

 

If I had to guess, I would guess if number of red was equal to yellows and greens, it would be red. Same with yellow and green. 

 

Discounting blanks and assuming the data is in a hierarchy, that would look something like this:

 

=IF(COUNTIF(CHILDREN(), "Red") >= MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow")), "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green"), "Yellow", "Green"))

 

Adding a fourth color would not be very difficult.

 

Hope this helps.

 

Craig

 

 

 

Christian Wells

Hi Teanna,

 

I was talking about using a Row as a summary row,  have the necessary SunIF/Countif formula's at the top of the columns.

For multiple statii, I have used;

1) converting the column counts to a text sting to show "Red=nn, Yellow=nn, Green=nn"

2) using other columns (text) to total the different statii.

 

The main thing to remember is to keep the Same fields across the sheets, keep the same summary row across the sheets...

Then the report will report the same information.

 

I also See Craig has assisted witht he formula, to sum the summary rows...

 

I hope this helps

Teanna Williams

Just able to get back to trying to resolve this nagging problem.  I do not have parent/children rows in my project sheet.  

 

When colors are equal, I would like to go with the lower priority color.  For example, if red and green are equal, I'd like an overall green light.  If red and yellow are equal, I would like an overall yellow light.

 

I'll keep trying, but your help is very much appreciated.

Teanna Williams

Here's what I have come up with so far in developing a solution for creating an executive report that shows a RYGG light indicator for the overall status of each project:

1. Select the largest number from the following range.

Using formula:

=MAX([Task Name]95:[Task Name]99)

 

2. Perform a manual check to flag "review" status if any of the above numbers are equal (then I can make manual changes if needed).  Using formula:

=IF(OR([Task Name]95 = [Task Name]96, [Task Name]95 = [Task Name]97, [Task Name]95 = [Task Name]98, [Task Name]95 = [Task Name]99, [Task Name]96 = [Task Name]95, [Task Name]96 = [Task Name]97, [Task Name]96 = [Task Name]98, [Task Name]96 = [Task Name]99, [Task Name]97 = [Task Name]95, [Task Name]97 = [Task Name]96, [Task Name]97 = [Task Name]98, [Task Name]97 = [Task Name]99, [Task Name]98 = [Task Name]95, [Task Name]98 = [Task Name]96, [Task Name]98 = [Task Name]97, [Task Name]98 = [Task Name]99, [Task Name]99 = [Task Name]95, [Task Name]99 = [Task Name]96, [Task Name]99 = [Task Name]97, [Task Name]99 = [Task Name]98), "Review")

 

3. As long as there is no "review" flag, then based on the line number of the maximum number idenitifed in step 1, list the status of the overall project.  Using formula:

=IF(ISBLANK([Task Name]112), IF([Task Name]111 = [Task Name]95, "Mostly Complete", IF([Task Name]111 = [Task Name]96, "Overdue", IF([Task Name]111 = [Task Name]97, "At Risk", IF([Task Name]111 = [Task Name]98, "On Track", IF([Task Name]111 = [Task Name]99, "Dates Not Set"))))))

 

4. List the color for each status using formula:

=IF([Task Name]111 = [Task Name]95, "Green", IF([Task Name]111 = [Task Name]96, "Red", IF([Task Name]111 = [Task Name]97, "Yellow", IF([Task Name]111 = [Task Name]98, "Green", IF([Task Name]111 = [Task Name]99, "Dates Not Set")))))

 

5. Show indicator light using formula:

=IF([Task Name]111 = [Task Name]95, "Green", IF([Task Name]111 = [Task Name]96, "Red", IF([Task Name]111 = [Task Name]97, "Yellow", IF([Task Name]111 = [Task Name]98, "Green", IF([Task Name]111 = [Task Name]99, "Gray")))))

 

Note: step 4 & 5 serve as double checks.

 

I will add these formulas to each project sheet then create a report.  I welcome any feedback that might improve what I have so far.  Thank you.