Change a checkbox state in one sheet based on the symbol in another sheet.

PMRobert

I want to check a box in my metadata sheet (restricted to checkbox column) if the overall health of my project is red. The referenced cell/column is restricted to symbols, green, yellow, and red circles. The formula I came up with is returning #INVALID REF. This will all end up in a report and will be an automated process through my templates, so it needs to remain general to just the cell that it intends to reference.

=IF(VLOOKUP([Project ID]@row, {Project Plan Schedule Health}, 1, false) = "Red", 1, 0)

I have tried a few other things and can't seem to automate this process. Can someone please help me understand what I'm doing wrong here?

PMRobert

Okay, I made a change to the formula, =IF(VLOOKUP([Project ID]@row, {Project Plan Range 1}, 1, false) = "Red", 1, 0) and now I am getting #NO MATCH.

Literally asked the question to finally solve a part of the problem. Now my checkbox isn't showing up as unchecked. I need a way to hide the no match error with the unchecked symbol since the error is technically correct.

PMRobert

I solved it.
=IFERROR(IF(VLOOKUP([Project ID]@row, {Project Plan Range 1}, 1, false) = "Red", 1, 0), 0)