Join collect based on date range
![gwson](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I have a helper column which works and collects expenses for events:
=JOIN(COLLECT({activityname}, {name}, [Full Name]@row), CHAR(10))
output:
CANS - AAN 2023 Advanced Method Conference - Registration - 09/13/23 - $226
NAHQ - National Assn for Healthcare Quality - Membership Renewal - 07/19/23 - $235
NONPF 2023 Fall Conference - Lodging - 11/02/23 - $427.14
I need to enhance this with a date range, so it only shows events after a specific date and onward. I am having issues with getting it to work:
=JOIN(COLLECT({activityname}, {name}, [Full Name]@row, {expensedate}, >=[Expense Date]$1), CHAR(10))
Answers
-
That looks like it should work, what kind of #ERROR code are you getting? Make sure that your ranges are all referenced correctly, and that date columns are all formatted properly, and if it's supposed to be a column formula, make sure you are using @row instead of $1.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
I located the issue, it will work as a cell formula, but not a column formula, I wonder why?
-
Column formulas cannot have a specific cell reference [Expense Date]$1. You need to use either @row, or you can put whatever is in that cell into a sheet summary field and then reference that in your formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!