IF/AND Function Across Different Sheets

Beckstrm
Beckstrm โœญโœญโœญ

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

image.png

using the "Type of Equipment" column and the "Mission Status" column and placing the result in sheet 2...

image.png

Suggestions?

Thanks,

Mike B.

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore Community Champion
    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

  • Kelly Moore
    Kelly Moore Community Champion
    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

  • Beckstrm
    Beckstrm โœญโœญโœญ

    THANK YOU!!!

    It works!

    Mike

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!