IF/And/Join/Collect

Hi Smartsheets community,

I am trying to create a summary field in a sheet that joins all the text from one column that meets these criteria:

has a created date less than 20 days old
has a label of "decision" in another column
OR a star for priority in another column.

I have a sheet used to track RAID entries, and leadership wants a dashboard that presents Decisions, Risks and Issues individually with a focus on priority and doesn't leave outdated information for review. I did create a report that does this, but they want a "streamline" dashboard (they want it to appear as text in boxes) and I can't figure out how to get each of these denoted separately without making individual reports for each RAID type.

I am new to Smartsheets, so there may be an easier way. The formula I have been working with is:

=IF(AND([Created Date]<TODAY(+20), [RAIDL_Type]="Decision", [Priority]=1), JOIN(COLLECT(DetailedDescription13:DetailedDescription56),;)," ")

Find more posts tagged with

Formulas

Dashboards

Sheet Summary

Join

collect

Accepted answers

Paul Newcome

I would suggest a helper column in the sheet (checkbox is recommended) that checks the box on every row that meets the criteria and then use the JOIN/COLLECT combo to filter based on this helper column.

=IF(AND([Created Date]@row> TODAY(-20), OR([This Column]@row = "This", [That Column]@row = "That")), 1)

Then your JOIN/COLLECT

=JOIN(COLLECT([Column To Join]:[Column To Join], [Helper Column]:[Helper Column], @cell = 1), "delimiter of choice")

All comments

Paul Newcome

=IF(AND([Created Date]@row> TODAY(-20), OR([This Column]@row = "This", [That Column]@row = "That")), 1)

Then your JOIN/COLLECT

=JOIN(COLLECT([Column To Join]:[Column To Join], [Helper Column]:[Helper Column], @cell = 1), "delimiter of choice")

Jbakertm

Thank you!

Jbakertm

Ok one more, If I want to create a helper cell to check box any item with a deadline within the next 60 days and the past 30 days how would I write that?

I have been using this to try and get anything 60 days out, but it is not working.

=IF(AND(DATEDIF([@EndDate], TODAY(), "d")<=60), "1", "")

Paul Newcome

There is no DATEDIF function in Smartsheet. You would need something more like this:

=IF(AND([End Date]@row>= TODAY(-30), [End Date]@row<= TODAY(60)), 1)

And for future reference, to allow for better searching capabilities here in the Community by other members, we do try to keep different requests in different threads. If you had another question similar to the JOIN/COLLECT or needed more clarification, it is ok to continue on this thread, but your most recent question is regarding checking a box based on dates.

While it may be part of the same solution you are building, it is very different from the original request and could therefore be put into its own thread if you weren't able to find an answer by searching other Community posts first.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.