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

chetg2000

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!

Matthew J McAteer

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

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

Genevieve P.

Hey @chetg2000

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

chetg2000

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.

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.