RGYB status - Return value based on blank cells in row

Carsonelli

I have a table where we have to report the status of a task for each month as the year progresses, so there is a column for each month and an overall status column (see image). We're using RGYB symbols to reflect these statuses. I'm having trouble figuring out the formula to pull the status of the latest month reported. For example, in row 3 in my image, the overall status should return a value of the blue symbol. Whereas, in row 4, the overall status would show a green symbol since nothing has been reported for Jun-Dec yet.

Any advice on how to achieve this?

Andrée Starå

Hi @Carsonelli

I hope you're well and safe!

Here's one way to do it.

Try something like this. (and continue the pattern)

=IF(Dec@row <> "", Dec@row, IF(Nov@row <> "", Nov@row))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Carsonelli

I hope you're well and safe!

Here's one way to do it.

Try something like this. (and continue the pattern)

=IF(Dec@row <> "", Dec@row, IF(Nov@row <> "", Nov@row))

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Carsonelli

That worked! I ended up just continuing the pattern like you said and ended up with this formula:

=IF(Dec@row <> "", Dec@row, IF(Nov@row <> "", Nov@row, IF(Oct@row <> "", Oct@row, IF(Sep@row <> "", Sep@row, IF(Aug@row <> "", Aug@row, IF(Jul@row <> "", Jul@row, IF(Jun@row <> "", Jun@row, IF(May@row <> "", May@row, IF(Apr@row <> "", Apr@row, IF(Mar@row <> "", Mar@row, IF(Feb@row <> "", Feb@row, IF(Jan@row <> "", Jan@row))))))))))))

Thank you!!

Leibel S

@Carsonelli

You can 'simplify' things by using the below:

=IFERROR(IF(COUNTIF(Jan@row:Dec@row, @cell <> "") > 0, INDEX(COLLECT(Jan@row:Dec@row, Jan@row:Dec@row, @cell <> ""), COUNTIF(Jan@row:Dec@row, @cell <> "")), ""), "")

Andrée Starå

@Carsonelli

Excellent!

You're more than welcome!