Drop Down Lists and calculations

[kpaveza]

Good morning, this may have been asked but I will proceed anyway.
I have a spreadsheet that we use to calculate costs of material that include the following.
Material cost + handling + freight = total cost.

Material cost is variable depending on vendor and manually inputted.
Handling is static
Freight is somewhat variable depending on vendor, some charge a percentage of material cost, some do not charge freight, some charge a flat rate based on how much is ordered, i.e. 5,000 pounds @ $.01 per pound.

The boss wants to create a drop down list based on vendor to calculate total cost which would include the freight.

I created a drop down list that will show the vendor, in the next cell it will populate the freight based on the vendor (created a vlookup).

The problem is, when a vendor is selected whos freight is based on a percentage of cost......not sure how to complete that formula.

 

[Roderick_E]

You'll need to build a list of the vendors (from which you can use for your dropdown list) and in columns next the vendors. You can place this on a hidden sheet if you want so that it is not showing on the "calculator". On that hidden sheet, you'll need some others columns.
VENDOR
FREIGHT BILL TYPE
FREIGHT MULITPLIER

Now back on the calculator sheet, you use your lookups to refer to the vendor depending on the dropdown selected.
On the calculator, in the freight column you would have a formula that determines what type of Freight billing to use. For example flat, percentage and such.
Then you calculated based on that. Something like:

=if(freightbilltype="flat", freightmultiplier,if(freightbilltype="percent",freightmultiplier*materialcost)) and on and on as needed.