RGYB status - Return value based on blank cells in row
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?
Best Answer
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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!!
-
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 <> "")), ""), "")
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!