If this and this match then yes - reference other sheet? Range issue?
Hello Smartsheet gurus (Smarties??)
I have 2 sheets that run separate forms for the purpose of auditing stock.
Sheet 1 is the audit sheet which has items like date and SKU.
Sheet 2 is a restock sheet which has date, sku and number of items restocked.
In Sheet 1 I would like to have a formula that looks at the date and sku being audited and show whether there is an entry on sheet 2 with the same date and sku.
I have tried this:
=IF(AND([SKU_1]@row = {Restock sheet Range 4}, Created@row = {Restock sheet Range 3}), "yes", "No")
It returns INVALID OPERATION error
Am I right in thinking that the issue is with IF statements and ranges? Would INDEX and MATCH be the way to go or some funky VLOOKUP??
Many thanks in advance!
Best Answer
-
Hello @Simon20444,
I came up with this solution, and I hope it helps.
In the sheet called "Restock Sheet" I have these data and columns:
In the sheet called "Audit Sheet" I have these data and columns:In the [Restocked] column I have the below formula:
=IF(COUNTIFS({Date}, Date@row, {Sku}, SKU@row) > 0, "Yes", "No")- {Date}: The date column on "Restock Sheet"
- {Sku}: The SKU column on "Restock Sheet"
- Date@row: The date on "Audit Sheet"
- SKU@row: The SKU on "Audit Sheet"
This formula uses a COUNTIFS to check if the date and the SKU from "Restock Sheet" match the date and the SKU in the "Audit Sheet" (this will return a number e.g 0, 1, 2, 3, etc..), then it uses the IF to check if that result is greater than 0, if true it displays "Yes", if false it displays "No".
Is this what you were trying to do?
Answers
-
Hello @Simon20444,
I came up with this solution, and I hope it helps.
In the sheet called "Restock Sheet" I have these data and columns:
In the sheet called "Audit Sheet" I have these data and columns:In the [Restocked] column I have the below formula:
=IF(COUNTIFS({Date}, Date@row, {Sku}, SKU@row) > 0, "Yes", "No")- {Date}: The date column on "Restock Sheet"
- {Sku}: The SKU column on "Restock Sheet"
- Date@row: The date on "Audit Sheet"
- SKU@row: The SKU on "Audit Sheet"
This formula uses a COUNTIFS to check if the date and the SKU from "Restock Sheet" match the date and the SKU in the "Audit Sheet" (this will return a number e.g 0, 1, 2, 3, etc..), then it uses the IF to check if that result is greater than 0, if true it displays "Yes", if false it displays "No".
Is this what you were trying to do?
-
Yes, thank you, this is perfect!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!