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
-
Try this:
=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, {Column of all Clients}, "Client Name"), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try this:
=INDEX(COLLECT({ColumnofCheck-inDate}, {Checkbox of 'Most Recent'}, 1, {Column of all Clients}, "Client Name"), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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. -
Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Sure
-
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@cellIt 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 🤣
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!