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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!