IF/AND Function Across Different Sheets
Hello,
I am trying to get information from one sheet to populate another sheet.
The formula I am trying to use is: =IF(AND({Equipment} = "MIGU-1", {Status} = "Open"), "Deployed", "Available")
I get an #Invalid Operation error.
On sheet 1 is the data I'm trying to separate out...
using the "Type of Equipment" column and the "Mission Status" column and placing the result in sheet 2...
Suggestions?
Thanks,
Mike B.
Best Answer
-
Hello @Beckstrm
Querying IFs across sheets require a different approach and some different functions.
Try this
=IF(COUNTIFS({Equipment}, "MIGU-1", {Status},"Open")>0, "Deployed", "Available")
This says if COUNTIFS (using your criteria) is greater than zero, it must have found something so return the true result, otherwise return the false result.
Kelly
Answers
-
Hello @Beckstrm
Querying IFs across sheets require a different approach and some different functions.
Try this
=IF(COUNTIFS({Equipment}, "MIGU-1", {Status},"Open")>0, "Deployed", "Available")
This says if COUNTIFS (using your criteria) is greater than zero, it must have found something so return the true result, otherwise return the false result.
Kelly
-
THANK YOU!!!
It works!
Mike
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!