Formula to display next/upcoming date from a list

Chris Walker

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!

Paul Newcome

Remove the @row references from your {Cross sheet references}.

[Cross Sheet Reference}@row

Chris Walker

https://community.smartsheet.com/discussion/comment/409319#Comment_409319

Thanks Paul. When I removed, I still get a response of '#INVALID COLUMN VALUE'.

Any other ideas?

Appreciate your support!

Paul Newcome

Make sure you are putting the formula into a date type column.

Chris Walker

Even if I want the eventual output from this formula to be the corresponding 'Holiday Name'?

Paul Newcome

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.