Have drop down list - Need to Change formulas based on drop down selected

I have a file that calculates Cost / Sell price for 5 different shipping speeds, but only shows the information for the one speed that is selected.

So rather than calculate in the background the 5 different speeds information, i am trying to make it ONLY do the one speed that has been selected from a drop down. (note: attached file only does calculation for 2 speeds, just to make things easier for now....)

I can write this with if/then statements, but I know that it would take forever, not just to calculate but to create the formula... but I am sure that there is a better way to do this.

test1.xlsx

In order for the file to fit into the upload i had had to remove most of the formulas but i think you will get the point with what is here.

Items that are in yellow are user input... everything else is to calculate behind the scenes.

So basically what i think needs to happen is if AB12 = 3 Day, then

Tariff goes to the table for 3 day, and pulls the appropriate number
cost disc looks at cell c9
sell disc looks at cell d9
and the cost and sell price calculate based on the necessary formula

which looks like:

Please Login or Register  to view this content.

Without using if/then statements, how do i get the tariff, cost disc, sell disc to change based off of the selection of ab14?

As much i could understand, please use below formula.

Formula:

Please Login or Register  to view this content.

Hope this will give your what you want. As the data C6:D10 in percentage format. You'll have need to change format in percentage.

Thanks
Ankur

WOW!

Thank you!!!

There are a couple of bugs... not sure now to work them out... BUT OMG you are AWESOME!

I have uploaded where it is at now..... When the "shipping method" now in cell V12 is Next Day Air Saver or Next Day Air it is pulling the same values for teh Cost and Sell % ( i understand WHY it is doing that... but dont know how to change that part of it). I know that I can change the name of one of them, but I am trying to avoid that if possible.

The Other issue comes with ground shipping.

The sell/cost discounts are different depending on the weight of the shipment (there is a 1-5lb, 6-10, 11-30 and 31+ discount option). How should the sheet be coded so that it will automatically pull the correct rate for ground?

Right now, it is set with if/then.... if 1<=weight<=5, then cost % = 6 for example.

Thank you again for your help!!!

::: removed :::