Automatically clear a flag symbol when a completed checkbox is selected.

Hi,

I have created a simple workflow for testing a product.

Within the row is a flag symbol column which the user selects to denote a fault. And when the fault is fixed a check box is selected at the end of the row to say it’s been completed.

The fault flags throughout the sheet are counted in a running total as is the completed check boxes.

What I would like to achieve is the fault flag symbol to be automatically cleared when the completed check box is selected by the user.

I have made some headway with the problem by creating a “dummy" flag fault column, which when selected by the user activates the "hidden" flag fault column. The hidden flag is also deselected when the completed checkbox column is ticked.

The formula I have in the hidden flag column is this

=IF([Dummy]1 = 1, IF(Completed1 = 0, 1, IF([Dummy]1 = 0, IF(Completed1 = 1, 0))))

This clears the hidden flag but obviously not the dummy flag.

I’ve have tried conditional formatting, but with no luck. Can someone guide me where I’m going wrong. I might be looking in the completely wrong direction, or trying to over complicate things.

Many thanks

Find more posts tagged with

Formulas

Conditional Formatting

Comments

Shaine Greenwood

Hello Scott,

Formulas only return results in the cells they're placed in, so a formula won't return a result in its own cell and in another cell.

With that being said, you can place this formula in the cells of your flag column:

=IF(Completed1 = 1, 0)

I'm not sure what you're using the "dummy" flag column for, but you can adapt this formula as needed by changing the cell reference.

Details on formulas can be found in our Help Center: https://help.smartsheet.com/articles/775363-using-formulas#if

Scott Lynch

Hi Shanie,

Thanks for the reply, but I dont think i'm making myself clear enough, let me start again.

I have a simple sheet workflow with a list of components that need to be tested.

Stage 1, A user tests a component.
Stage 2, If the component is found to be faulty, the tester selects the fault flag in the corresponding row.
Stage 3, This fault is then brought to the attention of an engineer who in turn repairs the component.

Stage 4, When the repair is complete the engineer who shared into the same sheet selects the completed checkbox.

Stage 5 (where im stuck) I now want the red flag to automatically clear once the completed checkbox has been ticked.

So im basically after a "switch" formula to switch the red flag off after the completed checkbox has been ticked.

I have already tried =IF(Completed1 = 1, 0) directly into the fault flag column.

But this formula is then voided or removed as soon and the tester "manually" selects the red fault flag.

Hence me going down the route of trying to solve this problem with a dummy column to auto trigger the main fault flag, and preserving the formula.

I hope this makes sense.

Kind regards

Scott Lynch

I have now found a solution to the problem by adding a simple dropdown column called Diagnosis. The value in the drop down is one word "Fault"

When "Fault" is selected via the dropdown list, the Red Fault Flag column is automatically triggered. It is then cleared when the Completed checkbox is ticked by the following formula, which is added directly to the fault flag column.

=IF(Diagnosis1 = "Fault", IF(Completed1, 0, 1))

I have then set up conditional formatting to highlight the fault row with a yellow background and red text. And when the checkbox is selected, the background is turned a light green with text light grey and strike through. Ive also locked the formula columns.

Although it may not be the most elegant solution, and i'd certainly be interested in other peoples ideas ...it works for me at this time. :-)

Many thanks