Can IF function be used with symbols and crosssheet 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 crosssheet range matches the Office@row, and if so, return the Green dot symbol.
Best Answer

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!