Can IF function be used with symbols and cross-sheet referencing?

I'm trying to create a formula that shows the Green and Red symbols based on information from a different sheet. Current formula looks like this: =IF({Open House Order Form Range 1} = Office@row, "Green", "Red"). I get an invalid operation error but I can't figure out why.

I'm wanting to know if any cell in the cross-sheet range matches the Office@row, and if so, return the Green dot symbol.

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@Erin Burley

You're on the right track, however your current formula is asking the system to see if the entire remote range equals the value in Office@row. That won't work. But you can embed a COUNTIF in there that will check this for you:

=IF(COUNTIF({Open House Order Form Range 1}, Office@row) >0 , "Green", "Red")

In English: Count the number of values in the Range 1 that are equal to Office@row, and if that number is greater than 0, set this cell to green; otherwise, set this cell to red.

All comments

Jeff Reisman

@Erin Burley

=IF(COUNTIF({Open House Order Form Range 1}, Office@row) >0 , "Green", "Red")

In English: Count the number of values in the Range 1 that are equal to Office@row, and if that number is greater than 0, set this cell to green; otherwise, set this cell to red.

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.