Formula for checkbox with dates

wendyb

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

Genevieve P.

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

wendyb

Thank you so much.

With a few tweaks this worked perfectly!