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),;)," ")
Best Answer
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you!
-
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", "")
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 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!