If this and this match then yes - reference other sheet? Range issue?

Simon20447

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!

RDRGSJ00

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?

RDRGSJ00

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?

Simon20447

Yes, thank you, this is perfect!