Contents of a Dropdown Multi Select Column

Options

Hi All

I need help in using the contents of a Dropdown Multi Select Column. The column has the following options

A

B

C

I want to use the actual content say AB to lookup another Smartsheet for a price for that selection. I am sorry if there is an obvious answer as I can’t find it.

Thanks

AV

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Can you provide a screenshot of how your price list is laid out? Sensitive/confidential information can be removed, blocked, and/or replaced with "dummy data" as needed.

  • Anil Varma
    Options

    Hi Paul

    Thanks for responding.

    I have kept this very simple - ideally I need to have the selection separated by say a slash or similar and then do a look up. Also need to understand what happens if the user selects B and then A versus A and then B.


    Anil Varma

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I assume your price list is columns 4 and 5? If that is the case, you will need to use line breaks in between the letters. Line break is the delimiter for multi-select columns.

    If you do not want to enter line breaks into your price listing, you would need to remove them before doing the lookup.

    I cannot tell based on your screenshot if you have a space in between A and B on your price list.

    If you do, try this...

    =INDEX([Column5]:[Column5], MATCH(SUBSTITUTE([Column2]@row, CHAR(10), " "), [Column4]:[Column4], 0))

    If you do not, try this...

    =INDEX([Column5]:[Column5], MATCH(SUBSTITUTE([Column2]@row, CHAR(10), ""), [Column4]:[Column4], 0))


    As for which order the selections are made in for the multi-select dropdown...

    Regardless of which order they are chosen in, they will always appear in the same order as they are populated in the column properties. So if your column properties are

    A

    B

    C


    And someone selects A and B, then it will always be in the order of

    A B

    even if they selected B first and then A.

  • Anil Varma
    Options

    Paul

    Thank you - perfect! (Not sure of the logic)


    1. Can I remove the spaces and insert a slash or dash - helps with the Lookup.
    2. As I cannot control the order of selection (at least 5 options) by the user, can I manipulate the answer so it is always in the some kind of order. Alternatively can I use the "OR" function to create a "helper" column which can help with the Lookup.


    Really appreciate your help with the first part - if the second part can be done, with your help I am there!


    Anil Varma

  • Anil Varma
    Options

    Paul

    Thank you - really appreciate your help - all works fine.

    I can see great advantages in using Smartsheet. I do find the training and reference material limited in allowing me to make a quick start. Except for your help, I am learning from trial and error - this forum is very helpful.

    As an aside, I have realised using an iPad in the UK for Smartsheet is not a good idea. Apple has decided to make inverted commas different from the US Keyboard and as a result I experienced a frustrating time wondering what I was doing wrong with my formulas. No problems with Excel however - I think Smartsheet need to look into this.


    Sincerely


    Anil Varma

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    As for the issue with the keyboard, feel free to either reach out to support or submit a product enhancement request. They may have some insight into that.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!