Is it Possible to Use INDEX-COLLECT When a Criteria Field is a Multi-Select Dropdown?
Hi, all;
I'm hoping you can help me with this one. Here's my boggle:
Sheet A collects Milestone and Parts data from a form and datestamps each entry. Sheet B has columns (Milestones) and rows (Parts) corresponding to the data entered in Sheet A, and the cells pull the date for each entry from Sheet A. For simplicity, we'll say the formula I've been using is this:
=INDEX(COLLECT({Date.ref}, {Milestone.ref}, 1, {Parts.ref}, [Parts]@row), 1)
This looks for a checked box in Sheet A to indicate which Milestone has been triggered, then references it with the part for its particular row in Sheet B, and retrieves the date that matches those criteria. When I select a single part using the Sheet A form, this works beautifully.
The problem comes when I select multiple parts for the same Milestone. The Parts column in Sheet A is a multi-select dropdown and can have dozens of parts selected at once. Instead of being able to find the correct part from amongst several in a cell, I just get an error.
I've done some research in the Community on using CONTAINS to resolve this. While I can't find a situation that matches mine exactly, I've been able to come up with this formula:
=INDEX(COLLECT({Date.ref}, {Milestone.ref}, 1, {Parts.ref}, CONTAINS(([Parts]@row), @cell), 1)
Not only does this look a little weird to me because of the cell references, but it just plain doesn't work. Right now I'm getting an #UNPARSABLE error. Does anyone know if I'm close? Do I need to use another cell reference instead of @cell? Should this even work? If not this, is there another way to do what I need? I'd appreciate any help you can give me.
Thanks...
Best Answer
-
For a multi-select, you may be better off using HAS versus CONTAINS. Where CONTAINS searches for a particular text string, HAS searches for a complete value within a multi-select field. (The help page for HAS was recently updated with guidance from me, and while still not perfect, it's better than it was!) Try this:
=INDEX(COLLECT({Date.ref}, {Milestone.ref}, 1, {Parts.ref}, HAS(@cell, [Parts]@row), 1)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
For a multi-select, you may be better off using HAS versus CONTAINS. Where CONTAINS searches for a particular text string, HAS searches for a complete value within a multi-select field. (The help page for HAS was recently updated with guidance from me, and while still not perfect, it's better than it was!) Try this:
=INDEX(COLLECT({Date.ref}, {Milestone.ref}, 1, {Parts.ref}, HAS(@cell, [Parts]@row), 1)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
THANK YOU! Thank you so much!
I figured there was a tool for this type of thing but there are so many tools I couldn't single this one out. It worked like a charm. It delivers an #INVALID VALUE error when there's no match, so I'll have to wrap this in an IFERROR statement, but this solves my issue in very simple fashion.
Mark this down as something I won't forget going forward. Thanks again, Jeff!
-
Glad it worked for you.
I ran into the same issue last year, and after a lot of experimentation I learned how HAS actually works, and submitted a help ticket for edits to the HAS help page. I live and die by the Function help pages and the Formula error page I have linked in my signature.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I'll be bookmarking those pages, myself. I tend to use the Smartsheet tooltips and Google quite a bit, but having the links directly certainly won't hurt.
Thanks again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!