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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 444 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 290 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!