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

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

Heather Duff

Hi @ABurks ,

Try this:

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

This should do what you're asking. It translates to:

If NA is checked, enter 1; otherwise, add 0.25 if GSS introduced is checked, add 0.25 if Customer Accepted is checked, and add 0.5 if Customer Completed is checked.

Hope this helps. Let me know if it works!

Best,

Heather

ABurks

Hi @Heather Duff, thank you for taking the time to respond! I'm not sure whether that formula worked because I used the formula that @Genevieve P. provided. Thank you both for the breakdown and thorough explanation of how to build the formula!