Populate a column based on another column with check box.

ABurks

Any help would be greatly appreciated.

What we would like to have happen is when a check box in one of the four columns (NA, GSS Introduced, Customer Accepted, Customer Completed) is checked, it will populate a Completion field in the corresponding row.

If NA is checked then Completion = 1

If GSS introduced + Customer Accepted + Customer Completed is checked then Completion = 1

If GSS introduced + Customer Accepted is checked then Completion = .50

If GSS introduced is checked then Completion = .25

I'm thinking I need a formula to put in the completion column that will update based on one of the four combinations described above. Not sure what the formula should be or how to string it together. Thoughts?

Genevieve P.

Hi @ABurks

You can use a Nested IF statement to automatically populate these percentages! Each of your statements above can be translated to a formula statement, like so:

If NA is checked then Completion = 1

=IF(NA@row = 1, 1,

If GSS introduced + Customer Accepted + Customer Completed is checked then Completion = 1

IF(AND([GSS introduced]@row = 1, [Customer Accepted]@row = 1, [Customer Completed]@row = 1), 1,

If GSS introduced + Customer Accepted is checked then Completion = .50

IF(AND([GSS introduced]@row = 1, [Customer Accepted]@row = 1), 0.5,

If GSS introduced is checked then Completion = .25

IF([GSS introduced]@row = 1, 0.25

Then if anything else is true (ex. no checkboxes), we want "" or "blank". Put them all together, with the closing parentheses for all IF statements at the very end, like so:

=IF(NA@row = 1, 1, IF(AND([GSS introduced]@row = 1, [Customer Accepted]@row = 1, [Customer Completed]@row = 1), 1, IF(AND([GSS introduced]@row = 1, [Customer Accepted]@row = 1), 0.5, IF([GSS introduced]@row = 1, 0.25, ""))))

Let me know if this works for you.

Cheers,

Genevieve