Final Value from Multiple Column Drop Down Lists
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
-
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.
Answers
-
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.
-
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!
-
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å
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 361 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!