Check a check box based on criteria from another sheet

LeodisStuart

I have 2 sheets that are tracking some tests. The first sheet contains all the individual test scenarios. The second sheet contains the end to end tests. Completing 1 end to end test will complete 1 or more scenarios.

I have a sheet that has a column called

"Scenarios Proven"

it is a 'dropdown column' that contains various scenario ids i.e AA1, AA2, ZZ1, ZZ2 It can contain more than 1,

The sheet has a 2nd column that has a check box.

My secondsheet has a column named "Scenario ID" which individually contains all the reference IDs and a 2nd column with a checkbox

I want to be able to check the check box in sheet 2 and for sheet 1 to check the check box of any rows that contain the "Scenario id" in sheet 2 in the "Scenarios Proven" column in sheet 1

I'm struggling to get the formula right. Any help would be greatly appreciated!! Thanks

Julie Fortney

Here's a suggestion:

Sheet 1

Sheet 2

Enter a column formula in Sheet 1's "Contains Checked Scenario" column:

=IF(CONTAINS(INDEX(COLLECT({Scenarios - Scenario ID}, {Scenarios - Checkbox}, true), 1), [Scenarios Proven]@row), true)

If you're not familiar, the {} indicate cross-sheet references. Let me know if you have additional questions.

LeodisStuart

Thanks for the help Julie, Unfortunately I can't seem to get it to work. I've changed my column names and sheet names to match your example (except the input column) but get the following error?

Just to clarify (as I'm not sure if I explained my want very well!! (Sorry)

Below are my 2 sheets

I want to test the e2e Journey and when I pass it (using the checkbox "Passed") I want the checkbox in the Scenarios sheet to be checked for the corresponding IDS listed in "Scenarios Proven"

Julie Fortney

Ah, ok! I think I had it backwards, where the scenarios sheet checkbox was the source for the Journeys sheet. If I understand correctly, if a journey passes, that means all scenarios that are part of that should also be passed individually. Makes sense!

The Journeys (aka End to End Tests in my example) sheet's checkbox is manual.

The Scenarios sheet will be the one with the cross-sheet formula:

=IF(COUNTIFS({End to End Tests Scenarios Proven}, CONTAINS([Scenario ID]@row, @cell), {End to End Tests Passed}, true) > 0, true)

This formula will count the number of rows on the Journeys sheet that contains the Scenario in the "Scenarios Proven" column with "Passed" checked. If that number is greater than 0, the box will be checked.

I hope that helps! Let me know if you have other questions.