Thread: BIG BIG CHALLENGE! Database Search & Calculation!!

Hi there,

I have a particularly sticky task ahead of me. My situation:

I am a company accountant. My firm delivers industrial components all across the country, and use many different couriers and freight companies. Every item we send out incurs a different charge, based on the dead weight and where it is going. We want to give our clients the absolute lowest price on delivery, but presently we have a very convoluted paper reference system from each company. Basically each destination has a base charge (x) and a set rate (r) per kilogram (k). Freight = x+(r*k)

In the end, I would like to be able to simply type in a postal code and weight, have Excel calculate the freight charges for all the companies, and then tell me which one is the cheapest.

If you can imagine, we have worksheets for each courier company. Columns 'A' are full of regions or post-codes, 'B' is full of base charges, and 'C' is full of rates per kilogram.

To make things disgustingly tricky, the companies now each have different percentage 'fuel levies' which are updated every month. We would need a cell to input that month's fuel levy, and have the end price reflect that too.

Then basically lock almost every other cell, protect the worksheet, and send it on a USB to Fort Knox so clueless people can't accidentally obliterate it.

Basically the only editable cells should just allow users to input weight, postcode and fuel levies, and have a big locked answer box which spells out which freight co. to use, and how much it will cost.

I feel nauseous just thinking about the task haha, but it would really wow my boss if we can get this thing running smoothly!

Any and every hint and tip would be hugely appreciated.

Thanks in advance!!

P.s. sorry for all the smilies, my coworker insisted I try and use them all at least once.