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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!