Check a Box based on a checked box in another column

KT_H

This is a formula I wouldn't typically struggle with but I am having experiencing formula block right now...

I am creating a sheet where new lines of data are entered for a test that is run. The unique identifier is the "batch number" and multiple lines of data may have the same "batch number" as tests are run until the product is in spec or is no longer changing. I have added 2 columns to support data management here. The first is "Last Sample" a checkbox manually checked once the last sample has been identified. The second, is "Remove from Report" a checkbox column that would house the formula that checks all tests run for the same batch number to see if Last Sample has been checked. If yes, then it checks last sample and I can use that column to remove all samples from a particular batch from a report.

Happy to provide screenshots if necessary for clarification.

Brent Wilson

@KT_H

Does this work for the Remove from the Report Column?

This one would check all values in the Remove from the Report Column if the Last Sample is checked

Remove from Report 1

=COUNTIFS([Batch Number]:[Batch Number], =[Batch Number]@row, [Last Sample]:[Last Sample], =true)

This one would check all but the Last Sample Checked Record

Remove from Report 2

=IF([Last Sample]@row = true, 0, COUNTIFS([Batch Number]:[Batch Number], =[Batch Number]@row, [Last Sample]:[Last Sample], =true))

You will notice how Rows 9,10 and 13 are left "unchecked" in the Second formula to leave the Last Sample in the DataSet

Don't know if you need it for record-keeping

You can go 1 further and create a workflow to "delete" the records from the table that are marked.

A quick way to do this is to create a Deleted Record Table which is a duplicate of your batch table and then create a simple workflow

You may have to go into the "deleted table" every once in a while and physically remove the records but this is also good as it archives some history

Kelly Moore

Hey @KT_H

Yes, a screenshot of the sheet would be helpful to see how the lab test data is collected (or mock up sheet if necessary to remove sensitive info). For example, is lab data in a single column or is it multiple columns of data?

To confirm, you want the Remove from Report checkbox to be checked when the Last Sample has all it's test results? Or are you looking for the Remove from Report checkbox to be checked when all of the relevant samples have test results, including the last sample?

Brent Wilson

@KT_H

Does this work for the Remove from the Report Column?

This one would check all values in the Remove from the Report Column if the Last Sample is checked

Remove from Report 1

=COUNTIFS([Batch Number]:[Batch Number], =[Batch Number]@row, [Last Sample]:[Last Sample], =true)

This one would check all but the Last Sample Checked Record

Remove from Report 2

=IF([Last Sample]@row = true, 0, COUNTIFS([Batch Number]:[Batch Number], =[Batch Number]@row, [Last Sample]:[Last Sample], =true))

You will notice how Rows 9,10 and 13 are left "unchecked" in the Second formula to leave the Last Sample in the DataSet

Don't know if you need it for record-keeping

You can go 1 further and create a workflow to "delete" the records from the table that are marked.

A quick way to do this is to create a Deleted Record Table which is a duplicate of your batch table and then create a simple workflow

You may have to go into the "deleted table" every once in a while and physically remove the records but this is also good as it archives some history

KT_H

Thank you @Brent Wilson that worked perfectly!