Using JOIN / COLLECT with contingent values in multiple columns

I am referencing a sheet with a list of materials and tests performed on the materials. Materials are constantly being added to the list. Each material may need one of 12 tests performed. Once a material is entered in the [Material] column, the sheet populates the 12 possible test columns with "Yes, Perform Test" or "No Test". Alongside each of the test columns is a [date complete] column and a [data review] column. If a material requires Test A, Test C, and Test D, a tech will perform the tests and enter the date the tests were performed. Then a manager will check the data and put an "X" in the data review field. Once all the tests have been performed, a report can be written to release the material.

I need to create a list of materials that need reports written for them. This list will be in a separate sheet. I've been creating similar lists with JOIN/COLLECT functions, but this one has more variables. I would like to COLLECT a list of materials, IF all possible tests have been completed AND data reviews have been checked off, but each material has a different set of tests. I tried a series of IF statements in the range criteria, as I've shown in the below example.

=JOIN(COLLECT({Material}, {TEST_A}, IF(CONTAINS("Yes", @cell), IF({TEST_A_Review} = "x", @cell, "NA"), @cell), {TEST_B}, IF(CONTAINS("Yes", @cell), IF({TEST_B_Review} = "x", @cell, "NA"), @cell))

What I'm trying to get it to do is this:

For a given material, check in the test column (i.e. {TEST_A}). IF that column contains the word "Yes", THEN check the data review column (i.e. {TEST_A_Review}). IF that column contains an "x", then the criteria I want to review the material against is @cell (meaning, the formula accepts this value as true). IF that data review column is not checked, THEN I do not want to add this material to the list. IF the test column does not contain "Yes", THEN I want the formula's review criteria to be @cell as well so that it doesn't reject a material row simply because the test didn't need to be performed.

Anyway, I've tried this a bunch of ways. I could theoretically do a gigantic nested if, but that makes me shudder. Any thoughts?

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!