Join collect based on date range

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))

Find more posts tagged with

Accepted answers

All comments

Jason Tarpinian

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.

gwson

I located the issue, it will work as a cell formula, but not a column formula, I wonder why?

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.