Join collect based on date range

gwson

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

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.