CHECK BOX

C. Srinidhi Ragunathan

Hello.

I have three columns that are dependent on one another. 

The first column contains the dropdown list: Received, Awaiting, Not Required. 

The second column consists of check boxes and the third column is % complete column. 

Is it possible to make the check box cell for a particular row blank/empty given the choice in the first column is not required ? 

The % complete column is dependent on the check box column. 

So if the check box cell is blank/empty, the % complete cell must also be empty/blank.

 

Are there any suggestions as to how to go about with this ?

 

Thank you

Andrée Starå

Hi,

Yes, it's possible, but it will delete the formulas if you change the values manually. Maybe it could be an option to use conditional formatting instead. If you activated dependencies and %Complete you can't have formulas in the % Complete.

Can you tell me more about the process?

What do you think? 

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

C. Srinidhi Ragunathan

Hello.

My dependencies are switched off, so I am able to enter functions in the % complete column. 

Under the check box column, I have written a function such that if the options are X, Y or Z in the first column, then the check box will be ticked. If the options are A,B or C then the check box won't be clicked. 

Is it possible that there can be a third option, wherein the check box cell will be completely blank. (as in not an unticked checkbox but an empty cell without a box ?)

 

Please refer to the image attached. The option of NOT REQUIRED must set the checkbox sell to a blank cell.

 

Thank you

 

 

 

Andrée Starå

Happy to help!

Yes, it's possible, but it's a workaround. It's a combination with conditional formatting. Can you share the formula you're using? 

Best,

Andrée

C. Srinidhi Ragunathan

I tried the following formula. Not sure if its the correct way to go about. 

=IF(OR([PRODUCT STATUS]x = "REPORTED", [PRODUCT STATUS]x = "SHIPPED", [PRODUCT STATUS]x = "RECEIVED"), 1, IF(OR([PRODUCT STATUS]x = "AWAITING", [PRODUCT STATUS]x = "TBD"), 0, IF( [PRODUCT STATUS]x= "NOT REQUIRED") , "")))

 

I also tried using conditional formatting, by changing the fill color to white, for the before mentioned condition. However it doesn't work.

Thanks

Andrée Starå

Hi,

Can you share the sheet with me? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

C. Srinidhi Ragunathan

I am sorry I am not permitted to share any sheet from my work account with a third party. 

Is it possible to explain it any other way ?

Andrée Starå

Ok

Can you share a screenshot?

Best,

Andrée

C. Srinidhi Ragunathan

Greetings Please Find attached screenshot.

The column with #1 is the Product status.

The column with #2 is the checkbox status.

Column 3 is the one with % complete. These are the main columns I am focusing on. 

 

Thank you

Andrée Starå

Try something along the lines of.

=IF(OR([Product Status]@row = "X"; [Product Status]@row = "Y"; [Product Status]@row = "Z"); 1; IF([Product Status]@row = "NOT REQUIRED"; "-"; 0))    

The same version but with the below changes for your and others convenience.    

=IF(OR([Product Status]@row = "X", [Product Status]@row = "Y", [Product Status]@row = "Z"), 1, IF([Product Status]@row = "NOT REQUIRED", "-", 0))

=IF([Checkbox Status]@row = "-"; 0)    

The same version but with the below changes for your and others convenience.    

=IF([Checkbox Status]@row = "-", 0)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Best,

Andrée

C. Srinidhi Ragunathan

Hello

Thank you. It has worked. 

 

Regards

Andrée Starå

Excellent!

I'm always happy to help!

Best,

Andrée