11

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

Comments

Andree_Stara

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

In reply to by Andrée Starå

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

 

 

 

In reply to by Andrée Starå

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

In reply to by Andrée Starå

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

Andree_Stara

In reply to by C. Srinidhi Ra…

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