How to checkbox when first and second criteria match in a dropdown list

Gina D

Hi fellow team members,

I can't figure how to check a box when first and second criteria match in a dropdown multi select list. Please reference below. Here's an example of what I am trying to accomplish. I want the column with the checkbox to 'Check' when the all the values in the 'List of Tests' column has been assigned in the 'Assigned Test' column based on the 1st criteria 'ID Material'. I'm familiar with Countif(s) functions, but I can't seem to get this right. For some reason, the dropdown multi select list is difficult to put together.

Please help.

Genevieve P.

Hi @Gina D

It sounds like you're looking to see if the "Assigned Test" cell matched exactly what's displayed in the "List of Tests" cell, is that correct?

If so, we can use an IF statement to see if the cells match (or = ) each other!

Try this:

=IF([List of Tests]@row = [Assigned Test]@row, 1, 0)

This will only check the box once the exact same selections have been put in the Assigned Test column. If you will have more selections in your "Assigned Test" column than what's in your "List of Tests" and you only need to verify that what's in the List appears, you can use the HAS function, like so:

=IF(HAS([Assigned Test]@row, [List of Tests]@row), 1, 0)

Let me know if this works for you, or if I've misunderstood what you're looking to do.

Cheers,

Genevieve

Gina D

Hi Genevieve P,

Thank you for the assistance. Can you include ID Material in the formula. For example, I want the checkbox to 'check' when all the tests has been assigned to QCS-001.

Hope this makes sense.

Genevieve P.

Hi @Gina D

Ah, I see! Are the two columns on the left (IDMaterial & List of Tests) a table to show what needs to be included?

If so, you could use an INDEX(MATCH function to find the List of Tests cell that matches the IDMaterial, and use the same IF statement to see if the returned List of Tests is the same as the Assigned Tests.

Try this:

=IF(INDEX([List of Tests]:[List of Tests], MATCH([IDMaterial2]@row, IDMaterial:IDMaterial)) = [Assigned Test]@row, 1, 0)

Let me know if this works!

Cheers,

Genevieve

Gina D

Hi Genevieve,

Thank you for taking the time to work on a formula. Unfortunately, the formula did not work.

I'd tried a different formula, but I can’t get my formula to work properly. However, the formula doesn’t work when the assigned test is deselected or deleted. Kindly reference below:

=IF(ISBLANK(IDMaterial@row), "", IF(COUNTIF([IDMaterial2]$1:[IDMaterial2]9, IDMaterial@row) > 0, IF(HAS([Assigned Test]$1:[Assigned Test]9, [List of Tests]@row), 1, 0)))

 

 

I want to check mark when all the tests found in the 'List of Tests' are assigned in the 'Assigned Test' column pertaining to the assigned QCS-001 in the 'IDMaterial' column. For example, QCS-001 (IDMaterial) should be checked mark, because all the tests listed in the 'List of Tests' have been assigned in the 'Assigned Test' column. However, QCS-002 should remain unchecked, since the tests are not all assigned.

I hope this makes sense.

Thank you,

Gina D.

Genevieve P.

Hi @Gina D

I think I finally understand what you're looking to do, thank you for your patience.

You have a table on the left (IDMaterial & List of Tests) which shows what tests are associated with each ID. Then you have two columns (IDMaterial2 & Assigned Test) which can individually assign one test at a time, or two at a time, to different IDs. You need to compare all of the collective tests assigned across multiple rows in the Assigned Test column to the Table in the top left which lists the total tests. Have I understood this correctly now?

If so, then you're right, the formula above that I suggested wouldn't work as it's looking to see if the correct number of tests were associated per row, versus adding these tests across all rows.

The first thing to do is to find out all the different tests assigned, based on this row's IDMaterial:

=JOIN(COLLECT([Assigned Test]:[Assigned Test], [IDMaterial2]:[IDMaterial2], IDMaterial@row), CHAR(10))

This will collect together all of the tests assigned to one ID into a text string. The CHAR(10) adds a line break between each selections.

Then once you've gathered this information, you can compare it to what's in the associated cell in that row for List of Tests assigned:

=[List of Tests]@row

So your formula will be looking to check the box if these two formulas match each other:

=IF([List of Tests]@row = JOIN(COLLECT([Assigned Test]:[Assigned Test], [IDMaterial2]:[IDMaterial2], IDMaterial@row), CHAR(10)), 1, 0)

Did this one work? Keep in mind that if there are duplicate selections in your Assigned Test column for that specific ID then it won't find a match.

Genevieve P.

Quick note - in further testing, the order of test selection would need to be the same to return the correct text-string to compare in your COLLECT formula. Is it possible that you will be assigning tests in random order?

Gina D

Hi Genevieve,

Yes, the order of tests will be in random order in regards to the big project my colleague and I are working on. For example, when I assign the tests for QCS-002, one week, it will be TEST01 and TEST03 in the same cell. The following week I will assign the remaining two tests of QCS-002, TEST02 and TEST04 in the same cell.

I will try your formula and see if it works, if not, I'll reach out to you soon.

Hope this makes sense.

Thank you.

Gina D

Hi,

Your formula almost worked, but as you have said, the formula works only when the list of tests are in order. You have been so helpful. Thank you again for taking the time to work on this. Let me know what other solutions may have.

Genevieve P.

Hi @Gina D

No problem! I'm happy to help, but I think we may need to re-structure your sheet in order to get the desired results.

For your reference chart, instead of having a multi-select cell house all the different tests per-ID, what if you set it up with a Parent Row identifying the ID, and Child Rows indicating a single Test each.

This would allow us to have an individual checkbox per ID & Test combination. Then your Parent Row would only check once all the Children have been checked, like so:

You'll see in my example above I've listed each individual test below the Parent ID, but I also have a summary up at the top. These are the formulas I used:

Column List of Tests, Parent Row:

=JOIN(CHILDREN(), " / ")

Column Checkbox, Parent Row:

=IF(COUNT(CHILDREN(IDMaterial@row)) = COUNTIF(CHILDREN(), 1), 1, 0)

Column Checkbox, Child Rows:

=IF(COUNTIFS([IDMaterial2]:[IDMaterial2], IDMaterial@row, [Assigned Test]:[Assigned Test], HAS(@cell, [List of Tests]@row)) >= 1, 1, 0)

Does this make sense? Since we list out the tests individually, you can use the HAS function to see if the column "Assigned Test" has that one specific test, along with the related ID.

There's a way we can build both the Parent and Child formulas into one long IF statement so you can make it a Column Formula, if you'd like.

Let me know if you're able to adjust your formatting like this, and if it would work for you!

Cheers,

Genevieve

Gina D

Hi Genevieve,

It's possible, but the project sheet is we are working on has been created similar to my example. We've been using the dropdown multiple list to reference the assignments for the analysts. At this point, making changing now, will require a large adjustment to the sheets.

For the meantime, I will use the checkbox without the formula to avoid additional changes to the major sheet.

Thank you.

Gina D'Amico