Current Task Formula based off certain criteria (symbols)

Frank_Mattiace

Hi, 

I want to create a formula that would read all rows have a Green symbol and read back the last task name that has green. This would have to be based off the row number and not dates, given not every task will have a start or end date. 

Attachment for context --> I would want the formula to read all of those tasks and deliver back "presentations task" since that is the last Green row. 

Paul Newcome

Would it be possible to have a few greens, a few yellows, then a few more greens? If so, how would you determine which is the last green? If not I would assume that row 2 will never be green before row 1, row 3 would never be green before row 2, etc.

 

Both scenarios are possible to account for in a formula, but they do require very different formulas.

Frank_Mattiace

Hi Paul,

Thanks for helping out. I'd say having green come after a yellow status would be unlikely. The goal is to show the last green row.  This would be based on the number of the row. So green in row 22 would be the last and the row I'd like to show. This formula would have to update as tasks are checked off as green. 

Paul Newcome

Ok. And are all of the rows you are looking at children rows of another, or are you strictly looking at them as individual rows with the intention of having the last one marked green displayed elsewhere on the sheet such as at the top or in a summary?

Brian W

I’m curious what Paul comes up with but here is what I would do:

I would create a helper column that checks if the current row’s cell in the RYG column is green and counts the greens in the cells below that one. If the cell is green and there are 0 green cells below it, it assigns a 1. This formula goes in the first cell of the helper column and assumes you have 50 rows of data:

=IF(AND(RYGColumn1 = "green", COUNTIF(RYGColumn2:RYGColumn$50, ="green") < 1), 1,0)

Then I would use INDEX/MATCH to find the 1 in the GreenHelper column and return the corresponding cell in the Number column:

=INDEX(Number:Number, MATCH(1, GreenHelper:GreenHelper))

Paul Newcome

@Brian

 

If they are all children rows that are being looked at, and the data will be displayed in the parent row along side the parent row task, and there will never be a green lower than a yellow, then I would be joining the parent row task text with the child row task with something along the lines of

 

="Parent Row Text - " + INDEX(CHILDREN(), COUNTIFS(CHILDREN([RYG Column Name]@row), "Green"))

 

Using the screenshot provided in the original post, the Parent cell would read something along the lines of 

 

Parent row text from row 18 - Presentations