How to update certain cells in column based on one cell's input and other column's same ID #?

shondaconnor0413

I have a very simple test script testing tracker located here:

https://app.smartsheet.com/sheets/mC9H8jRfG8G4GgfqJwmRMvqVpRWvM7pphq6FpGp1

I want to be able to do one of the following:

1. Update the Status column for all testers with the same test ID to "Duplicate" once one person tests and puts "FAIL" in the PASS/FAIL column.

2. Do not copy defects to defect tracker sheet if one of the rows with same "ID" are marked as "FAIL" in PASS/FAIL column already.

I have tried some IF formulas as well as some automations but can't seem to get it to do exactly what I want.

Paul Newcome

You would need a helper column with a formula along the lines of

=IF(COUNTIFS([Test#]:[Test#], @cell = [Test#]@row, [PASS/FAIL]:[PASS/FAIL], @cell = "FAIL") > 0, "FAIL")

Then you would set up a change cell automation to change the pass/fail column to fail when this new helper column says "FAIL", and whatever system you have set up to copy the rows would need a condition included that this helper column is blank.

shondaconnor0413

Here is a snapshot of the sheet itself for easier viewing and so you can see the columns I refer to above:

Paul Newcome

You would need a helper column with a formula along the lines of

=IF(COUNTIFS([Test#]:[Test#], @cell = [Test#]@row, [PASS/FAIL]:[PASS/FAIL], @cell = "FAIL") > 0, "FAIL")

Then you would set up a change cell automation to change the pass/fail column to fail when this new helper column says "FAIL", and whatever system you have set up to copy the rows would need a condition included that this helper column is blank.

shondaconnor0413

Thank you Paul!!

Worked perfectly!

Paul Newcome

Happy to help. 👍️