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

Erin Burley

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.

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.

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.