I need to match order numbers between 2 sheets and add checkmark for complete
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!
Answers
-
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
-
Thank you Mathew, I tried the formula, but I get #UNPARSEABLE in the result.
-
Hey @chetg2000
Can you post a screen capture showing how you typed in the formula?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
-
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.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!