How to return the value of another sheet cell, taking into account, values in 2 other cells & sheet?

Options
MLB
MLB ✭✭✭
edited 04/04/23 in Formulas and Functions

Hello,

I am trying, with a formula, to automatically have a cell checked IF the cell from another sheet is checked too, AND if their ID is the same. Here is my case:

  • I have one "Test Cases" sheet (sheet A).
  • When the tester selects Fail in sheet A, the row is copied to the "Issue List" sheet (sheet B) with an automation rule. Almost everything is copied but some additional information are needed.
  • The tester receives then an email asking to complete the row in sheet B (automation rule).
  • Once the row is filled out with all required information, the cell "Issue logged" is automatically checked in sheet B (automation rule).
  • I would like to have the cell "Issue logged" in sheet A, checked too, taking into account the Test ID, to avoid the cell to be checked if the row doesn't correspond to the actual test ID.

I tried IF/AND or IF/CONTAINS but I have #INVALID DATA or #UNPARSEABLE or #INVALID OPERATION

Examples of what I tried:

=IF(AND({Test ID} = [Test ID]@row, {Logged Issue}, 1), 1, 0)

=IF(AND(CONTAINS([Test ID]@row, {Test ID}), {Logged Issue}, 1), 1, 0)

=IF(CONTAINS([Test ID]@row, {Test ID}), (AND({Logged Issue}, 1), 1, 0)

=IF(CONTAINS([Test ID]@row, {Test ID}), 1, 0) --> This one works but only checking if I have the Test ID info in both sheets. The "issue logged" column is not taken into account.

I figured I would ask help from the community before giving up this idea! 😅

Thank you in advance for your help!



Tags:
«1

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!