IF/AND Function Across Different Sheets

Options

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.

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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 ✭✭✭✭✭✭
    Answer ✓
    Options

    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 ✭✭✭
    Options

    THANK YOU!!!

    It works!

    Mike

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!