How do I source a date from another sheet to correlate with the first selection in a multi select

Options

How do I source a date from another sheet to correlate with the first selection in a multi-selection drop-down menu in another sheet?

I have multiple expeditions listed in a multi-select drop-down menu (column named):

Trip Code

When I select one or more expeditions I want the date from the first expedition in the selected list to auto-populate with the date from the related Trip Code within this column (column named):

Trip Start Date (office)

from another sheet called:

01_Trip Codes_Expeditions List and Room Numbers

the Trip Start Date (office) would be sourced from a column named the same Trip Start Date (office) connection to the Trip Code related to the specific expedition code listed in the drop-down menu.

----

I also need to be able to create a formula to source the end date (column named):

Trip End Date (office)

from another sheet called:

01_Trip Codes_Expeditions List and Room Numbers

from the last expedition listed in the multi-select drop-down menu (column named):

Trip Code

and add it to (column named):

Trip End Date (office)

the Trip End Date (office) would be sourced from a column named the same Trip End Date (office) connection to the Trip Code related to the specific expedition code listed in the drop-down menu.

making my head hurt...

I really hope someone can help me.

Kindly,

Natalia

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    Options

    Hey Natalia!

    As far as the drop-down is concerned, sadly, you can't do that. It's been something we've all wanted for ages, but Smartsheet has yet to add this feature. If I'm understanding you correctly, you'd like to be able to select a list of options within a drop-down cell that is sourced from a different sheet, right?

    You can't even do that with a drop-down cell in the same sheet. This sort of "conditional" drop-down selection isn't available within Smartsheet natively. I could write you something that could pull from dates and update the column via the Smartsheet API, but it's not something that Smartsheet does "out of the box".

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • Natalia Baechtold
    edited 12/07/21
    Options

    Hi Brett,

    My source sheet is NOT a multi-select drop-down menu for Trip Code.

    In my source sheet:

    01_Trip Codes_Expeditions List and Room Numbers

    I have three columns

    Trip Code

    Trip Start Date (office)

    Trip End Date (office)

    I'd like to be able to pull information into my Trip Specific sheet (the 5 digit code at the front of the sheet will change for every trip sheet created):

    22ECU Guest-Application, Waivers and Finance

    within the Trip Specific sheet, I'd like to be able to select multiple trip codes relevant within a Trip Code column (multi-select drop-down menu) and for the first trip code to cue the date for that trip from the Source sheet as the:

    Trip Start Date (office)

    and the last trip code selected to cue the:

    Trip End Date (office)

    Kinda complicated but it is hard to keep up to date across 5 Trip specific sheets if I cannot keep the data relevant to the first sheet in a line of booking sheets relevant to the trip.

    Thanks for trying to help,

    Natalia

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Natalia Baechtold

    I hope you're well and safe!

    How many specific trip sheets can you have, and how often are there new ones? I'm asking to figure out if it's ok with a manual step or if we could automate it with a so-called helper sheet.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.