Final Value from Multiple Column Drop Down Lists

Options
GeorgeM
GeorgeM
edited 04/03/24 in Formulas and Functions

Hi

I am new to smart sheet, so creating a formula is not my strong point. I am creating a sheet where in the column named: Type Of Shoot, has a drop down list. Then in the column name: Duration, also has a drop down list. I then want the column named: Fee, to produce the value (cost) based on the drop downs selected from columns "Type of Shoot" and "Duration".

Image one shows the sheet where it must be done, the drop downs have been created. The second image shows another sheet where i created the values to pull/reference from:

It would be great if I could be assisted with a formula that populates the Fee column based on the selections made in "Type of Shoot" and then the option chosen from Duration being "Half Day, Full day or 24 Hours".

Thank you

Best Answer

Answers

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

    Hi @GeorgeM

    I hope you're well and safe!

    You could connect the sheets using cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure. When you update the source sheet, it will reflect on the destination sheet.


    Here's an example of how it's structured.

    =VLOOKUP(CellThatHaveTheValueToMatch@row,{RangeThatHaveTheValueToMatch*}, ColumnNumberWithTheValueYouWantToShow, 0)

    *Where the first column is the ColumnWithTheValueToMatchAgainsTheCell


    I used a VLOOKUP in my example, so you only have to create one range. If we used INDEX/MATCH, you would have to create four ranges.

    Try something like this. (create the range first)

    =IF(Duration@row = "Half Day", VLOOKUP([Type of Shoot]@row, {FeeTable}, 2, 0), IF(Duration@row = "Full Day", VLOOKUP([Type of Shoot]@row, {FeeTable}, 3, 0), IF(Duration@row = "24 Hour", VLOOKUP([Type of Shoot]@row, {FeeTable}, 3, 0))))


    Did that work/help?

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @GeorgeM,

    This is absolutely doable with some cross sheet formulas. If you're not familiar with how to create them, then this should help walk you through it:


    You can then use a formula like this:

    =IF(Duration@row = "Half Day", INDEX(COLLECT({Half Day}, {Shoot type}, [Type of Shoot]@row), 1), IF(Duration@row = "Full Day", INDEX(COLLECT({Full Day}, {Shoot type}, [Type of Shoot]@row), 1), IF(Duration@row = "24 Hour", INDEX(COLLECT({24 Hour}, {Shoot type}, [Type of Shoot]@row), 1))))@GeorgeM

    Where the cross sheet references (in the { } brackets) refer to the relevant column on your data sheet. You can name them something different, but you will need to create these they can't simply be typed in until they have been made.

    Hope this helps, but if I've misunderstood anything or you've any problems/questions then let us know!

  • GeorgeM
    Options

    Thanks both so much, I managed to link it and it works perfectly.

    The final fourmula was:

    =IF(Duration@row = "Half Day", INDEX(COLLECT({Half Day Ref}, {Type of Shoot}, [Type of Shoot]@row), 1), IF(Duration@row = "Full Day", INDEX(COLLECT({Full Day Ref}, {Type of Shoot}, [Type of Shoot]@row), 1), IF(Duration@row = "24 Hours", INDEX(COLLECT({24 Hour}, {Type of Shoot}, [Type of Shoot]@row), 1))))

    I appreciate the very quick and helpful responses @Nick Korna and @Andrée Starå

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

    @GeorgeM

    Excellent!

    Happy to help!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!