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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!