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.
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!