Final Value from Multiple Column Drop Down Lists

GeorgeM

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

Andrée Starå

@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!

Andrée Starå

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.

https://help.smartsheet.com/function/vlookup

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

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!

Nick Korna

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:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

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

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å

@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!