Vlookup ?? with my mileage sheet

**carlsson** · 10-01-2007, 11:38 AM

Hi I've been given a mileage sheet at work to use to calculate my journeys
I cant help thinking it would be easier if i could have a way of calculating the mileage from one location to the other just by inputing the name in a couple of cells to produce the result in a third cell ?? I've attached an example of the sheet. Regards C

**NBVC** · 10-01-2007, 12:02 PM

It would be better if you attached a zipped excel sheet instead.

also, what do the numbers mean? Do you have destinations down the first column and destinations along the top row, so that the intersection of 2 destinations is the mileage between?

**carlsson** · 10-01-2007, 05:26 PM

Hi Yeah sorry that text file was very misleading I'll post a zipped sheet.
The sheet is usually given out printed but i stumbled across the excel version so i thought

there must be a better way

I've used Vlookup before but when you see the sheet and it's layout you'll see why i'm not sure of this. I feel i might have to strip out the data and change the layout, which would be a lot of work. I guess i was kinda hoping that someone might see it and know a solution. C

**oldchippy** · 10-01-2007, 05:29 PM

To post a zipped example of your sheet, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**carlsson** · 10-02-2007, 03:58 AM

Hi Heres the sheet mentioned HTH C

**NBVC** · 10-02-2007, 07:46 AM

This, for me, is an unusual mileage sheet. Can you please explain how it works? and what inputs you would have and what you would expect as an output...

use examples which can be found on the sheet.

**oldchippy** · 10-02-2007, 07:59 AM

Hi NBVC,

I think this is how it works:-

ADAMSON to CAMERON is 12 miles
ADAMSON to FORTH PARK is 20 miles
CAMERON to FORTH PARK is 8 miles
FORTH PARK to LYNEBANK is 14 miles

**NBVC** · 10-02-2007, 08:09 AM

Originally Posted by oldchippy

Hi NBVC,

I think this is how it works:-

ADAMSON to CAMERON is 12 miles
ADAMSON to FORTH PARK is 20 miles
CAMERON to FORTH PARK is 8 miles
FORTH PARK to LYNEBANK is 14 miles

Oh, I see. Makes sense. Thanks oldchippy.... I'll try to work with that.

**NBVC** · 10-02-2007, 10:16 AM

Okay,

I came up with something...based on oldchippy's interpretation of your chart (which I hope is correct).

See attached...

In the Input sheet, you select (from a dropdown list that I included ) the Start location and the Finish location... the total miles will appear underneath.

Note: I added a 0 under the last location so that my formula would work..since it depends on a number being present under each location name. You can change the font format colour to white to make it invisible...

If you add locations you will need to alter the formula (all you have to do is just change all the 79's in the formula to the new last row number).

If you do change the formula in any way, you must confirm it with the CTRL+SHIFT+ENTER keys, this will make it an array formula and { } braces will appear.

Hope this is what you were after..

Note: You can go in any direction.. ie. you can pick a location on the left as start and a location on the right as a finish or vice-versa....

**carlsson** · 10-02-2007, 11:10 AM

Hi

Thanks thats excellent I guess thats what you would call nested IF's ??

does just the job

C

**oldchippy** · 10-02-2007, 11:35 AM

Superb answer NBVC

Vlookup ?? with my mileage sheet

LinkBack

Thread Tools

Rate This Thread

Display

Vlookup ?? with my mileage sheet

excel sheet

Attached sheet

mileage sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions