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

Mik

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)

cheers,

Mik.

Mike Wilday

Have you considered using vlookup? 

The vlookup function could search the column with the RYG symbols and when it finds "Red" it would deliver the column next to it. 

See here for more info... https://help.smartsheet.com/function/vlookup

Mik

Thanks for getting back Mike, I did have a brief play with vlookup, and its description sounds right, but couldn't for the life of me make a formula work, keep getting 'incorrect argument set'

Any chance you might be able to give a hint formula..? 

Mike Wilday

=vlookup("Red", [Insert RYG Column Name Here]:[Insert Comment Column Name Here], 2, false)

Try replacing your column names in this formula. See if that works. 

Mik

That worked. Thanks so much Mike 

Mike Wilday

Glad I could help out.