I need to match order numbers between 2 sheets and add checkmark for complete

Options

I have an API that brings in new orders to a sheet called "new orders"

I also have the API that brings in orders as they are shipped in a sheet called "orders shipped"

I want to be able to find all the order numbers in "new orders" that match the order number in "orders shipped" so I can check complete on my "new orders" sheet.

I can't get my head wrapped around the formulas needed to do this. Any help is greatly appreciated!

Tags:

Answers

  • Matthew J McAteer
    Options

    Hi @chetg2000,

    Try the following formula in your Complete checkbox column:

    =IF(HAS({Orders Shipped Order Number}, [Order Number]@row), 1, 0)

    The {Orders Shipped Order Number} is a cross sheet reference to the column where you have Order Number on your Orders Shipped Sheet.

    Hope this helps.

    Matthew

  • chetg2000
    Options

    Thank you Mathew, I tried the formula, but I get #UNPARSEABLE in the result.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @chetg2000

    Can you post a screen capture showing how you typed in the formula?

  • chetg2000
    Options

    Thanks guys! I kept playing with it, and I didn't have all the syntax correct in the formula. It is now working. Do you know if the formula I am using would interfere with automation? My next task is a workflow that moves the row to another sheet when the "complete" is checked. For some reason, it is moving all rows whether they are checked or not. I did a test and added a "dummy" column. I did a workflow that that triggered when the "complete" is checked that would automatically check the "dummy" column. But it also automatically checks all the rows whether they are checked complete or not.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @chetg2000

    Can you post a screen capture of how your workflow is set up?

    Actions that modify sheets (such as move row workflows or record a date) cannot be triggered by formulas that use cross-sheet formulas or cell-links. You'll likely need to use a time-based workflow with conditions that look for the check or not.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!