Formula for checkbox with dates
Hi there,
I have a summary section in my smartsheet. One of the summary rows should highlight any fdob dates that are upcoming. There may be multiple items that will need to be listed in a single summary cell.
I want the summary section to show 'Floor # FDOB Golive DD/MM/YY' by marrying the following items:
fdob column - checked box
task name
end date of task
Really struggling with this and would appreciate some help.
Thank you
Wendy
Answers
-
Hi @wendyb
The way I would do this is to first add a helper column to your sheet to bring together all the cell data for one row into one cell.
For example:
=IF(FDOB@row = 1, Task@row + " " + End@row)
This will check if the box is checked in the FDOB column, and if it is, return the Task name with a space and then the End Date.
Then once each row contains one cell that has your combined data (if FDOB is checked), you can create a JOIN(COLLECT formula in your summary section to join together the cells in the helper column that meet your criteria.
I would personally use CHAR(10) as the separator between your different row values as this will break them out into a new line in the cell (if you use wrap-text).
The structure of a JOIN(COLLECT is as follows:
=JOIN(COLLECT([Helper Column]:[Helper Column], [Criteria Column]:[Criteria Column], "Criteria"), CHAR(10))
Let me know if this makes sense and works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you so much.
With a few tweaks this worked perfectly!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!