INDEX Collect Based on Text String & Check Box

I keep going back and forth between Unparseable and Incorrect Argument trying the different ways I'm seeing these formulas done in another threads. I can't show the exact sheets. But, essentially, I have a client progress report that we want to show the 'most recent date contacted' and a sheet that we log all client interactions that lists all clients and has a checkbox for most recent contact. I'd like to pull the most recent contact date for a client from the log into the client specific sheet.

So, very generically, it's like this where I want the formula to go in the cell for the 'Last Check In Date' under date of most recent activity. I created a cell that contains the client name b/c I'd like to avoid a text string if possible since this is a template.

I have all of my reference columns set. Tried these formulas which are unparseable:
=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, {Column of all Clients}, HAS(@cell [row name with cell containing client name]1)), 1)

=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, {Column of all Clients}, [row name with cell containing client name]1 @cell), 1)

Incorrect Argument

=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, {Column of all Clients}, "Client Name", 1), 1)

=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, CONTAINS({Column of all Clients}), 1), 1)

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, {Column of all Clients}, "Client Name"), 1)

All comments

Paul Newcome

Try this:

=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, {Column of all Clients}, "Client Name"), 1)

paige_pj

That gives me Incorrect Argument.

And, I'm going to repeat this formula for other 'most recent dates', so I was hoping to have the client name 1 spot in the sheet since we will have a sheet per client.

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

paige_pj

Sure

paige_pj

Well, unfortunately, I found that the issue was my column reference…so, it works now!

~~However, I would love to replace the "ClientName" with a cell reference if possible. Would that work somehow? It still doesn't work if I use [row with client name]1@cell~~

It was the @cell part! I should have just had it cell reference, and now, I got that to work! I can't believe I fought this formula for so long when it was user error the whole time 🤣

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.