How do I search another sheet to see if a column is blank off of a column of search term?

Trae Dodson
Trae Dodson ✭✭
edited 06/22/22 in Formulas and Functions

I'm trying to figure out how to have my master inventory list go look at another sheet and determine if a certain tool is out or in. In being the default while out would only be triggered if the date received column is blank.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Trae Dodson

    I hope you're well and safe!

    One way would be to use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Trae Dodson
    Trae Dodson ✭✭
    edited 06/24/22

    @Andrée Starå

    I already use a match and index formula to pull the descriptions the issue is I am trying to look at a column within one sheet to verify if it is blank and if it is I want to output on the other sheet a certain value which is confusing me since I don't know how to incorporate it with these formulas.

  • Amy Flores C
    Amy Flores C ✭✭✭
    edited 01/23/24

    @Trae Dodson not sure if you ever ended up with a solution, but sounds like you need something like what I made.

    The first picture is my master asset inventory sheet with the below formula in the Green box (it's actually a Column formula)

    =IFERROR(IF(AND([Project Number]@row = "SHOP", OR([Asset Condition]@row = "RED", [Asset Condition]@row = "YELLOW")), "Maintenance Required", IF(AND(NOT(ISBLANK([Assignee Check-In Date]@row)), [Project Number]@row = "SHOP"), "Available", IF(AND(NOT(ISBLANK([Assignee Check-Out Date]@row)), [Project Number]@row = "SHOP"), "In Transit", IF(ISBLANK([Project Number]@row), "", IF(NOT([Project Number]@row = "SHOP"), "Checked-Out", ""))))), "")

    The Second picture is my intake sheet done with a form with conditional logic. I also have a couple more helper columns to help determine the newest entry, from the oldest. The oldest one gets flagged for archive, and an automation will removed the flagged columns. So my intake sheet never has duplicates of an assets barcode.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!