Copying comment text into another cell but only when a certain value is triggered

edited 12/09/19 in Formulas and Functions

Hoping you can help! 

I'm trying to setup a formula where if I have a Red RYG ball in a cell of a task row then the comment next to it copies into a different cell (on the same sheet) for reporting. 

I know I could just create a report which looks at values in those columns and trigger only when there's a red ball along with its comment, but its to go into a Rollup Sheet along with certain other things. 

So my Rollup Sheet will look at certain cells, and when any task is flagged red (will always only be one task that is flagged red, not multiple) one of the cells it looks at is if there's any reds (and I've already done the formula which flags any reds into one 'reporter' cell), what I'm after is the comment in that 'red task's' row to copy down into a separate cell

Hope that makes sense! (screenshot attached to help explain)



Screen Shot 2019-01-28 at 17.17.34.png


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!