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

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?

Find more posts tagged with

Formulas

Accepted answers

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.