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

MLB

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!

Austin Smith

@MLB

Try this:

=if(index(sheet A, match([Test ID B]@row, [Test ID A]:[Test ID A], 8)=1,1,0)

should make a check on B if the matching id is checked on A

MLB

Hi @Austin Smith ,

Thank you for your answer.

Here I want the check in the "Logged Issue" column of Sheet A to happen if there is a check in the "Logged Issue" column of Sheet B (and not the opposite). I tried to change the sheets info from your formula, but I always got #INCORRECT ARGUMENT SET. Also in your formula, is the info about the "Logged Issue" column missing?

Thanks.

Paul Newcome

Try this:

=IFERROR(INDEX({Sheet B Checkbox Column}, MATCH([Test ID]@row, {Sheet B Test ID}, 0)), "")

MLB

Thank you @Paul Newcome . So there is no error message anymore, but it doesn't return the check box in Sheet A, while it is present in Sheet B 😕

Paul Newcome

The IFERROR statement is masking an (most likely) UNPARSEABLE error. It looks like you have one too many opening curly brackets there before the very first range.

MLB

Yes I realized when sending my previous comment and fixed it, but it's still blank. I've been told that it's maybe because Smartsheet doesn't like checkboxes.

Paul Newcome

I use it all the time on checkboxes.

Lets get rid of the IFERROR for now to see what it is telling us.

=INDEX({Sheet B Checkbox Column}, MATCH([Test ID]@row, {Sheet B Test ID}, 0))

MLB

If I manually check the checkbox in Sheet B, the box is Sheet A is checked. BUT, it broke my automation in Sheet B: before, the checkbox cell was automatically checked if certain conditions were met. Now, I just have a #NO MATCH and I can't check the box anymore; as if the cell was not a checkbox anymore. Instead, I have a #NO MATCH info.

Paul Newcome

That particular error indicates an issue with the ID column(s). DO you have any entries that do NOT start with a leading zero?

MLB

I don't believe no. The {Sheet B Test ID} is an auto number one, as well as the [Test ID]@row in Sheet A. They all start with 0. The {Sheet B Checkbox Column} is a checkbox column so there is no 0.

Paul Newcome

Lets try a quick test...

Insert a temporary text/number column and use this formula:

=IF(ISNUMBER([Test ID]@row), "NUMBER")

Then create a filter to show only rows where this column is "NUMBER". Do you have any rows popping up on either sheet?

MLB

Everything is blank.

Do you think it's because the column used is an Auto number one? See below my screenshots; when using the Auto number column as ([Test ID]@row), the value is blank. When using the Text/number column as ([Test ID]@row), the value is NUMBER.

Using the Auto Number column:

Using a Text/number column:

Paul Newcome

As long as both sheets end up with the same result, then there should be no issue. The leading zeros make them text values, but I wanted to make sure they were ALL text values so that we are comparing text to text.

What does this give us?

=INDEX({Sheet B Checkbox Column}, MATCH("004", {Sheet B Test ID}, 0))

MLB

Hi Paul,

I still have the same error message #NO MATCH. Maybe the issue comes from the fact that the Test ID column is an Auto number one?

I had to deliver my testing process so I took another route and won't track the logged issues (check box) automatically.

I really appreciate your help and support.

Paul Newcome

FOr some reason your last screenshots didn't load the first time I saw your comment. I meant for you to evaluate the auto-number column with the temporary check and not a separate column.