Create a Cell that can summarize what's missing in other cells of the same line?

Kevin Scatton

Hi!

Sorry in advance, I may over-write / overexplain because 1. I'm lame at explaining and 2. English is not my first language ^^'

In my work, I need to do a release checklist, with multiple checks. Each check is a cell saying for example "Date OK?", or "Price in database?" etc etc. I fill this cell with "OK" if it's done, or I write why it's not done yet to have a clear view of what I still have to do. For example if the price is not in the database I'll put "Ask partner by mail again" or anything, this cell is "free" when I write something, except for the "OK" which means it's good.

Here is an example of how it was in Google Sheets (of course the colors were set automatically \o/):

I'm trying to move this file to Smartsheet because it is very annoying to transfer informations between my main file in Smartsheet with all the data (used by the whole company) and this checklist on Google Sheets (and I have to keep tracks of all changes in my Smartsheet file to report them in the release checklist one, yada yada yada).

Now, knowing that Smartsheet is powerful, I am wondering if I can make a column that will resume the state of a range of columns (the ones I use for my release checklist). In the case of the picture above, this summary cell will say "Issue sending build" or something like this. And I'd like it to check if there is an issue for all the cells in a range, and write a little standard text (probably the column name) in this summary cell, to have a very quick glance of what I still need to do. I have ~20 cells to fill for my release checklist so I'd like to avoid to scroll in my Smartsheet all the time ^^

I thought aligning multiple "If" like Excel authorize (cf capture below) but it doesn't seem to work.

If someone has an idea or want some more explanation, or just insult me and my stupid questions, you're more than welcome :D

On a related topic: it was easier in Google Sheets to say "If this cell starts with "OK", then put it in green" and apply this to a whole range of cells, it seems I have to do it column per column in Smartsheet?

Thank you!

Andrée Starå

Hi Kevin,

Formula

Try something like this.

In my example I've added what I want the cell to show if the value isn't OK in the first row of the sheet.

=JOIN(COLLECT([Date OK?]$1:[Build Sent?]$1; [Date OK?]@row:[Build Sent?]@row; @cell <> "OK"); CHAR(10))

The same version but with the below changes for your and others convenience.

=JOIN(COLLECT([Date OK?]$1:[Build Sent?]$1, [Date OK?]@row:[Build Sent?]@row, @cell <> "OK"), CHAR(10))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Make sense?

Did that work?

Conditional Formatting

Yes, you have to select all columns or which columns you'd want it applied to.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi Kevin,

Formula

Try something like this.

In my example I've added what I want the cell to show if the value isn't OK in the first row of the sheet.

=JOIN(COLLECT([Date OK?]$1:[Build Sent?]$1; [Date OK?]@row:[Build Sent?]@row; @cell <> "OK"); CHAR(10))

The same version but with the below changes for your and others convenience.

=JOIN(COLLECT([Date OK?]$1:[Build Sent?]$1, [Date OK?]@row:[Build Sent?]@row, @cell <> "OK"), CHAR(10))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Make sense?

Did that work?

Conditional Formatting

Yes, you have to select all columns or which columns you'd want it applied to.

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Debbie Sawyer

HI Kevin

Interesting scenario - I'm sure Smartsheet can offer you a number of ways of doing this.

You could set up a checklist sheet which has a whole load of IF functions that could pull data through to it depending on the values in the master and say Done once complete...(as you suggested)

You could set up a Report based on the main sheet and use the comments column to add what you are going to do to chase that record and not have a checklist (if the criteria for the cell isn't outstanding it wont pull through...) (This might work depending on the layout of the main sheet)

You could write some Update Request Automations from the main sheet, but this would be for you to update the main sheet live and not have a checklist...

Lots of ways

Which appeals to you?

Kind regards

Debbie

Kevin Scatton

OMG ANDREE, that's a smart way to take care what I need! It supposes I have to write more clearly what is the issue in each cell (instad of writing "Waiting" in the "Build" column, I should write "Waiting for the build") but I think I can work with this :D It really works, that's so cool, I didn't know those formulas !!

Sorry, I'm REALLY excited :D

And if I just need Smartsheet to check if it CONTAINS "OK" (for example I like to put "OK not needed" in some cases), is it still possible? If too complicated I can work with just "OK".

Debbie :

Thanks for your solutions, but the one Andree proposed seems to be the one fitting the most \o/

I also thought of setting up a huge load of If functions (I asked the question in my request) but I don't really know how to do it easily / clearly (I have around 15 rows to check for my checklist :x). That's also why just working with a comment column is not enough because there are too many things to check and I need something visual (so a line with a lot of "OK" in green, and a "summary" cell saying what's missing).

I'm actually already working on a report, that will only show the columns of this checklist, because our main sheet is around 50 rows and annoying to visit :D

Andrée Starå

@Kevin Scatton

Haha! Thanks! Happy to help!

You could update my formula to only look at the same row instead and then it would collect what you've written in each cell if it's not OK.

I'd suggest if possible to use a drop-down list with OK and other usual statuses, but you also have the possibility to write out your own details.

It's possible to modify the formula. See below.

Try something like this.

=JOIN(COLLECT([Date OK?]@row:[Build Sent?]@row; [Date OK?]@row:[Build Sent?]@row; NOT(CONTAINS("OK"; @cell))); CHAR(10))

The same version but with the below changes for your and others convenience.

=JOIN(COLLECT([Date OK?]@row:[Build Sent?]@row, [Date OK?]@row:[Build Sent?]@row, NOT(CONTAINS("OK", @cell))), CHAR(10))

Debbie Sawyer

Great stuff - glad you got it sorted :)

Kevin Scatton

Ooooh thank you, it works so great.

The first column is my summary. I actually even made a little change, I'm checking if the 2 first letters are "OK" (with the LEFT function), just in case I have to write "joke" in this cell for example, and it works great.

So happy to be able to move my release checklist into Smartsheet thanks to you all!

Andrée Starå

@Kevin Scatton

You're more than welcome!

I'm always happy to help!