Need help with mileage log

[PhoneTeck]

Need some help.

I downloaded this template from Microsoft's website.

http://office.microsoft.com/en-us/templates/TC060884621033.aspx?pid=CT101481361033

I'm needing the help of someone who is tech savvy and is really smart with formulas etc. in Excel.

The idea is to modify the two fields that says odometer start and odometer end and replace the two fields with the text Building start and Building end. Then when you click the building start or building end field you will be presented with a building name to select from. We have an excel sheet with all of the distances in miles between the buildings we go to. I'm wanting to incorporate it to where when I click start building, it will present me with a drop down list and I can select the building I'm driving to and then click the building end and select the building I'll be ending at and have it calculate the miles based off information that I already have in a excel spreadsheet that work has provided.

 

[SkipVought]

Hi,

Here's a good link that addresses dynamic lists based on a previous selection.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops...

http://www.contextures.com/tiptech.html

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PhoneTeck]

Thats a great site, if only I knew enough about what I was doing that site would be a great help. I have no knowledge of creating stuff in excel, more than anything I was hoping for someone who could do what I can not.

 

[SkipVought]

Time to learn.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

On that site look at Data Validation Basics and then Item 2 in the list at the bottom of that page.

Gavin

 

[PhoneTeck]

I managed to get the drop down list to work, when I click the drop down it lists out different buildings to choose from.

The purpose of the list is to calculate the mileage from say Building A to Building B. I have on a piece of paper that says if your going to Building A to Building C the mileage is this, but if your going to Building A to Building D then the mileage is this. How can I take the info I have on paper and based off of the two columns of drop down boxes that I have created, know this data? The title above my first drop down box says Start Building and below it you get a drop down box of buildings. The next row over's title is End Building and below its title you can get a drop down box of that building.
Where do I input the mileage data so that when I get this all setup the row next to it's title can say Total mileage for the title and then have it output the correct mileage driven based off of information that I enter, but not sure where I enter this data and how to reference it.

Please help

 

[SkipVought]

You need a table like this...
[tt]
Bldg A Bldg B Mileage
[/tt]
So you have a list of ALL BUILDINGS for your combobox source.

Based on the selection, you generate the second list based on the data in the table above.

The second selection, give you the OFFSET for the row of data that THAT COMBOINATION (A to B) is in: column C containing the mileage.

This could also be done quite easily, VERY simply, using the AutoFilter on the table structure I just posted.

First select the A building. Then select the B building and VOLA (as we say in Texas)!!! No formulas, no extra controls. Less than 15 seconds!!!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PhoneTeck]

Skip would you be willing at looking at what I've got? I have to be missing something.

 

[SkipVought]

send to

ii36250 at bellhelicopter dot textron dot com

and I'll look.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PhoneTeck]

Thank you, sent.

 

[SkipVought]

I tried to take out the odometer start and odometer end because I don't need those fields but ever time I do it, it messes up the columns to the right of it."

Why do you care? The formula refers to odometer reading and you don't want that. Your mileage will be from your tables.

Did you try constructing the table as I suggested (i.e. scrap the template)

Exactly what do you hope to get from this sheet and how will you use it?

"The other thing I noticed is, the field that says rate per mile wont let me enter in 58.5 cents, it doesn't like the .5"

Select that cell. Right-click Format Cells - Change places from 2 to 3



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}