Formula to place a text status based on completion of tasks
Hello,
I haven't worked with formulas enough to get started with what I am needing. I have 3 tasks that need to happen, 'A', 'B', and 'C', before the job can be marked done. There is a checkbox column for each 'A', 'B', and 'C' showing if they are all needed. Regardless of whether or not they are all needed, they need to be performed in order from 'A' to 'B' to 'C'. There are also columns to check off once 'A', 'B', and 'C' are completed and a status column.
I'd like to write a formula that checks to see if each are needed and then as the task gets completed, change the status to the next needed task. For example if all are needed, then before any get completed the status would be 'A' or the first needed. If only B and C are needed and B isn't done then the status would be B, etc. If all are completed then the status would be "Done". If B or C is needed, the formula needs to check to see if the preceding task was done or needed.
I believe I can do this with formulas but I am unsure of where to start and any help would be greatly appreciated!
Thank you,
Lk
Best Answer
-
I admit that this will be a difficult thing to parse out because of the variability of the checks. You will essentially have a lot of if statements and have to keep up with where you are in order to make the logic work. For instance, just to map it logically, just for the A you'll have to check:
If A is Needed -> is A Checked -> No -> Then A
-> Yes -> Then Is B Needed checked
etc, etc, etc
Here's an example of just "If A is checked then check for B" that I wrote up really quickly.
=IF([A Needed]@row, IF([A Completed]@row = 0, "A", IF([B Needed]@row, IF([B Completed]@row = 0, "B", "Done"), "Done")), "Done")
Answers
-
I admit that this will be a difficult thing to parse out because of the variability of the checks. You will essentially have a lot of if statements and have to keep up with where you are in order to make the logic work. For instance, just to map it logically, just for the A you'll have to check:
If A is Needed -> is A Checked -> No -> Then A
-> Yes -> Then Is B Needed checked
etc, etc, etc
Here's an example of just "If A is checked then check for B" that I wrote up really quickly.
=IF([A Needed]@row, IF([A Completed]@row = 0, "A", IF([B Needed]@row, IF([B Completed]@row = 0, "B", "Done"), "Done")), "Done")
-
Thank you David! That will get me started!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!