Formula to display next/upcoming date from a list
Hello, I'm looking to have a formula to display the next/upcoming date in the list. The formula is on another sheet (Sheet = "Helper Calculations"), so I need to reference another sheet (the one in the table below - Sheet = "Holiday Dates") within the formula. On this sheet, I can confirm the Date column is a Date type.
I was thinking it might look like this (below), but it returns #UNPARSEABLE.
=MIN(COLLECT({Date}@row, {Date}@row, @cell > TODAY()))
To round things out, I would like the formula to output the 'Holiday Name', not just the date.
Any help is greatly appreciated!
Answers
-
Remove the @row references from your {Cross sheet references}.
[Cross Sheet Reference}
@rowCome see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul. When I removed, I still get a response of '#INVALID COLUMN VALUE'.
Any other ideas?
Appreciate your support!
-
Make sure you are putting the formula into a date type column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Even if I want the eventual output from this formula to be the corresponding 'Holiday Name'?
-
If you are outputting text then it would go into a text/number column, but your MIN/COLLECT is outputting dates which have to go into a date type column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!