MIN / COLLECT / Other column

I'm trying to learn smartsheet. Just to practice I create a simple grid with the data of my employee.

I have these columns :

First name (text), Last name (text), Birthday (date), Birthday current year (date), Full name (text), Two days before (checkbox)

The last three columns are column formulas :

Birthday current year =>

=IFERROR(DATE(YEAR(TODAY()), MONTH([Birthday]@row), DAY([Birthday]@row)), "")

Full name =>

=[First name]@row + " " + [Last name]@row

Two days before =>

=IFERROR(IF(AND([Birthday current year]@row <= TODAY(2), [Birthday current year]@row >= TODAY()), 1), 0)


Ok, so in page summary I created this formula :

=MIN(COLLECT([Birthday current year]:[Birthday current year], [Birthday current year]:[Birthday current year], >=TODAY()))

So far so good, everything seems to work, I have the next birthday in this cell...

But I now I want to have in another summary field, the full name of the employee of the previous birthday...

I can't make it work, could you point me in the right direction, please?


Just to let you know I translate manually my column name, so maybe I did some typo but everything is working fine...

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭

    point me in the right direction

    Is your hang up on pulling in the employee name? Or is it in identifying the previous birthday?

    If it is identifying the last birthday, I would create a helper column in the employee data grid, something named Birthday_Past with the formula:

    =IF(([Birthday current year]@row) < TODAY(), [Birthday current year]@row, "")

    The MAX of that column is the most recent birthday. From there you can MATCH and INDEX to get the employee name.

  • Pulling the employee name...

    At the end, I would like to have the next birthday date with the employee name in another summary field...

  • Finally, my question is : "In a grid, give me the next upcoming event, return the date and any other column.


    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!