Multiple IF statements/Managing large formula.

Matt Desjardin

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!

Jeff Reisman

@Matt Desjardin

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.

Matt Desjardin

@Jeff Reisman

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

Bek T

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