Multiple IF statements/Managing large formula.
Hello Smartsheets Community,
I was curious if anyone had some input. I've developed a sheet that runs different formulas for different "vendors" in a column to get a final price based on the vendors data. It's become a very large IF statement as there's many vendors now (if vendor is Vendor 1, *do this* etc). I'd like to make an easier way to add vendors into this formula as we get new ones throughout the year. Is it at all possible to say, have a sheet that I could enter the vendors and data into, and "match" a formula onto another sheet instead of using a long formula? For example, instead of writing a formula for each vendor in one large formula, be able to call on a different formula for each vendor in another way? Hopefully that makes sense, looking to make it easier for other team members to add vendors into the formula. May or may not be possible.
Thanks for any thoughts!
Answers
-
This is mostly possible, with some tweaks. Much of it depends on what your formulas are doing, where they are getting data values from, etc.
Say Vendor A has a price multiplier of .71, and Vendor B has a multiplier of .75. A lookup sheet listing this data in Vendor and Multiplier columns could be useful in calculating prices on an order sheet:
Vendor SKU Qty Price Total
Vendor A Widget001 2 $8715.00
The Total column could use an INDEX/MATCH to bring in the multiplier value and apply it to Qty x Price:
=Qty@row * Price@row * INDEX({Lookup Sheet Multiplier Column Range}, MATCH(Vendor@row, {Lookup Sheet Vendor Column Range}, 0))
The result is a total of $12,375.30
If you could share some screenshots, specifics of what your formulas need to do, I could help you with the logic and syntax.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you for your detailed reply I appreciate it. Going to work with this formula and see if I can tweak it for what I need. Some vendors require slightly different formulas, so if I can't get it to work I'll forward some images of my project! Thanks again for your time.
Best,
Matt
-
Did you ever get any further with this? I have a similar issue, where we want people to see their carbon emissions from choosing one place to the next. Having a formula with one origin alone to all the destinations is large enough, not sure of the best way to have multiple origins with the same destinations? Did you use a reference sheet? It was too much for the AI assist too, it seems
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!