Freight Calculator

I'm building a freight calculator and am considering some professional consulting options, but before I do that I wanted to see if I could overcome this one problem. If I can, I think I might be able to complete the calculator myself.

Here's my conundrum:

A potential customer enters "80802" for zip code and "Solomon" for store.

StoreLocation_________ City_______ State_____ Zip______ Distance
Solomon__________ Arapahoe _______CO ____ 80802_____ 270
Garden City_______ Arapahoe_______ CO _____80802_____ 143

The formula (or series of formulas) I'm looking for would then refer to the following hidden sheet and return Arapahoe, CO and a distance of 270 miles from Solomon.

Any insight would be very appreciated. Thanks.

Yes, It's currently in a grid mileage chart (atleast I think it's set up as you ask). I went ahead and attached the data.

Martindwilson, I'll take a look at that. Looks promising so far! I'll be dissecting it and trying to absorb...

@ shg: I'll keep that in mind. If there's ever a chance I'll edit this on a pre-2007 version of office, would that give me trouble?

@ Martindwilson:I noticed that the zip code must be entered as text using the apostrophe... Do you mind me asking why it has to be done that way?

Originally Posted by shg

Do you need to know the distance between any pair of stores?

How many stores are there?

Nope- I don't need the distance between the stores.

There are only these two stores. Which store the customer picks just depends on which one is closest to their location (or if they happen to be in the area).

Originally Posted by shg

If you need to be able to select any pair of stores, then in Excel 2007, you have sufficient columns to represent inter-store distances much more compactly as a single table rather than as one tab per store.

Here's a question- how do I move those tables from their separate tabs/worksheets to the first tab (with the freight calculator)?

I know I can copy/cut & paste, but if I highlight and drag the data then the formulas that go to those worksheets will transfer with the data- while just pasting won't carry the formulas with them.

(thanks martindwilson- that worked perfectly)

this is exactly the same sort of thing i want to do but I have about 12 different departure towns that i want to be able to choose from and then enter a destination town and then see the distance. if I used your formula martindwilson I would have to repeat it 12 times for my 12 stores. Would this work? If not is there another option? I am already using a vlookup formula for just one store.

Hi guys,

It would great if you help me with the freight calculator I am trying to prepare.
The table in the attached file will be hidden. My client should choose country and zip code (note that I will operate with around 10 countries) and based on the purchased item's weight [50 or 100 kg - I need kind of VLOOKUP formula here, because the weight can vary from 50 to 10 000 (more columns are coming)], I have to calculate what would the charges be.

Thank you so much!

Noted. "Freight calculator issue" thread created. Thank you