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.



Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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)


    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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!