Using multiple checkboxes to output a list.
I have a Smartsheet with 7 columns, all with checkboxes....they are tasks for a project.
What I'd like to do is summary the tasks (in one cell) based the boxes that were checked. For example, if my boxes are:
Take out the trash (checked)
Mow the lawn
Clean room (checked)
Wash dishes (checked)
Walk dog (checked)
Vacuum car
Water plants (checked)
I'd like a cell with the output to be:
Tasks completed today were:
- Take out the trash
- Clean room
- Wash dishes
- Walk dog
- Water plants
Outstanding work:
- Mow the lawn
- Vacuum car
Is this possible or is it beyond the limitations of Smartsheet?
Answers
-
You want the entire results in a single cell? given the tasks are in a column named "task", the date is in the column "date", and the checkbox column name is "checkbox" you can use the formula below.
="Tasks completed today were:"+join(collect(task:task,date:date,@cell = today(),checkbox:checkbox,1),", ")+" Outstanding work: " + join(collect(task:task,date:date,,checkbox:checkbox,0),", ")
If you want to break it apart or spread it out that is definitely possible too.
-
If your tasks are spread out across multiple columns represented by checkboxes, and you want ONLY those that have been checked...
Insert a new row (I will use top of the sheet as this example and assume the columns are in the order you have posted).
In row 1, enter the actual text for each task (the text that you want to pull together).
Then in your column where you pull each field to...
="Tasks completed today were:" + CHAR(10) + UNICHAR(65106) + JOIN(COLLECT([First Task Column]$1:[Last Task Column]$1, [First Task Column]@row:[Last Task Column]@row, 1), CHAR(10) + UNICHAR(65106))
-
Hi Jason,
Happy to help. L & Paul are correct you could utilize a JOIN(COLLECT()) to achieve your desired logical goal of putting in one cell all of the values that are checked and in another all of the tasks still to be completed. The above-given examples are good examples of how you could create the formula. You can also reference this Help Center article here for further examples of this formula and its syntax: https://help.smartsheet.com/function/collect
You can also achieve this goal simply by utilizing a Report, no formula needed. Reports give you the ability to reference sheet data based on specific criteria. Reports are further outlined by this Help Center article: https://help.smartsheet.com/articles/522214-creating-reports
Have a wonderful day. Thank you for contacting Smartsheet Support.
Cheers,
Eric
Smartsheet Technical Support
-
@Eric M Oliveira I don't think a report would work in this case because the poster specifically said that they wanted all checked options pulled together into one cell.
A report may compile rows matching specified criteria, but it does not allow the joining of data into a single cell.
@Jason Goldstein I didn't mention it above, and I missed the window to edit my post.
CHAR(10) is a line break which will take the next set of data to below the previous set (you need to use the "wrap" feature on the cell for it to display that way)
and
UNICHAR(65106) is simply the bullet.
-
Thanks for all the help. I used the formula given by Paul Newcome, and I'm getting the following:
-
You would need to add that "helper" row where the text is entered.
Insert a new row (I will use top of the sheet as this example and assume the columns are in the order you have posted).
In row 1, enter the actual text for each task (the text that you want to pull together).
-
@Paul Newcome - is there a way to summarize into a column instead of a row? For my example, I have a form submission for each row that is exported to Excel and merged into Word so ideally, we would have 1 column that summarizes all the checkbox columns the person selected on the form. I would want the line break and bullet coding as well. The end product is a job description and the requester is selecting competencies/skills they want listed. Thank you!
-
@Alisia Anderson Could you manually enter some data into a sheet to reflect what you are trying to accomplish and post a screenshot of it?
