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)


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!