Populate a column based on another column with check box.

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?

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    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
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!