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

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

@StevieSango

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)

All comments

Jeff Reisman

@StevieSango

=INDEX(COLLECT({Date.ref}, {Milestone.ref}, 1, {Parts.ref}, HAS(@cell, [Parts]@row), 1)

StevieSango

https://community.smartsheet.com/discussion/comment/335342#Comment_335342

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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/335350#Comment_335350

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.

StevieSango

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!

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