First off, I'll upload the sample data here.
Notice on the first tab called "Calculations" there are lines called:
Enter weight in kg:
as sample data, I've put "New Zealand" and "1.3kg"
Lines 6, 7 and 8 have these headings:
Each of these lines is going to pull data from the three data sheets of the same names.
Columns B, C, D, E, and G are simple labels that are being pulled from one of the other sheets (namely: ISO, Continent or Zone, Base Cost, Cost/kg and Shipping ID)
Column F is the important one, which is a calculation of shipping cost based on all the other factors.
Now check the tab called "EMS".
The first country is "Argentina"
Notice that "Argentina" appears several times in the list, but each with different weights. For example, in the following weight ranges:
0 to 0.5 kg
0.5001 to 1 kg
1.0001 to 1.5 kg
1.5001 to 2 kg
2.0001 to 2.5 kg
2.5001 to 11 kg
11.0001 to 30 kg
So if I type "Argentina" into cell B3 and a weight like 3.51kg into cell B2, all of the cells from B6 to G8 should magically fill in for me. Get it?
Anyway, there are about 4 more "sheets" of rates to include and more data. But this, so far, is the skeleton structure of my database I'm working on.
Can someone please help with the formulas for cells B6 to G8? After I kind of get the idea, I'll be fine with finishing the rest. The idea is I don't want to go through all the work of entering the data if the formulas aren't going to work, or if I'm going at the whole project all wrong.
Thanks in advance!