gregholl

asked on

# Help with looking up data in an Excel Database

First off, I'll upload the sample data here.

Shipping_rates.xlsx

Notice on the first tab called "Calculations" there are lines called:

Enter weight in kg:

Enter country:

as sample data, I've put "New Zealand" and "1.3kg"

Lines 6, 7 and 8 have these headings:

EMS

DHL-INTL-TRACKING

DHL-INTL-NO-TRACKING

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

and

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!

Greg

Shipping_rates.xlsx

Notice on the first tab called "Calculations" there are lines called:

Enter weight in kg:

Enter country:

as sample data, I've put "New Zealand" and "1.3kg"

Lines 6, 7 and 8 have these headings:

EMS

DHL-INTL-TRACKING

DHL-INTL-NO-TRACKING

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

and

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!

Greg

ASKER

AWESOME! LOOKING GREAT.

1) Some of the data sheets have as many as 1280 lines. Maybe I should change that to 1500 just to be sure??

2) I changed the formula for column to:

=IF(AND(D6<>"",E6<>""),D6+E6*$B$2,"")

(notice D6+E6*$B$2)

BUT...

one small thing I forgot to mention:

The number at $B$2 needs to be rounded up to the next WHOLE number.

For example, if the weight entered is 3.1kg, then the postage calculation is based on 4kg (the next whole number). Get it?

Please help with that tweak and we're done! :D

1) Some of the data sheets have as many as 1280 lines. Maybe I should change that to 1500 just to be sure??

2) I changed the formula for column to:

=IF(AND(D6<>"",E6<>""),D6+

(notice D6+E6*$B$2)

BUT...

one small thing I forgot to mention:

The number at $B$2 needs to be rounded up to the next WHOLE number.

For example, if the weight entered is 3.1kg, then the postage calculation is based on 4kg (the next whole number). Get it?

Please help with that tweak and we're done! :D

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks Friend. You rock! :D

Open in new window

I have set it for 1000 rows, as using entire columns will make the calculation slow.The calculation formula for Shipping cost is Weight * Base cost * Cost/kg

=IF(AND(D6<>"",E6<>""),D6*

See sheet for the other formulas.

EDIT: With file

Shipping_rates.xlsx