Mileage Log To Record Specific Distances?

Nov 2, 2013

I'm creating a mileage log for my work as a traveling art teacher. Most templates have you just input the number on the odometer when you started and when you finished. Since I can never remember to check the odometer when I am getting in and out of the car I thought it might be easier to just record the distance from each location once and then record that number of miles each trip. I usually travel the same route so the mileage is always the same.

I have an array of numbers right now, with 3 columns listing my 3 most common start locations. Then I have 5 rows listing the 5 schools I travel to for work, and the distances from each start to each end in the array. On a separate sheet I have my log.

What I would like to be able to do is select a start location and an end location from a drop down list on the log and have the number of miles automatically appear in the miles column. Then I can double this for the return trip and total it at the bottom and calculate my expense.

I am having trouble writing a formula that works for all 15 possible distances, and I don't quite know how to write it so that it references my array and finds the correct distance for those two specific choices. So far I have the drop down menu's pulling from the array but I'm stuck at how to index the array using the information that is coming from the drop down list.

Since I travel to these locations multiple times I think it would be a lot faster than looking up the distance every time and typing it up manually.

View 7 Replies


ADVERTISEMENT

How To Record Actual Mileage When There Are Blank Cells Between Recordings

Apr 5, 2009

The spreadsheet i am working on requires mileage between last trip and previous trip.

Unfortunately there will be blank cells between as shown below ...

View 9 Replies View Related

IF Formula- Mileage Cells To Automatically Equal The Mileage

Feb 13, 2009

This is for mileage purposes. I have two sheets. 1st sheet I have Date, From, To, Mileage. I want the Mileage cells to automatically equal the mileage it is from and to. Right now I have it setup like this on the 1st sheet B7 is Date, C7 is From and D7 is To and E7 is Mileage. I have a drop down menu for C7 and D7 for our facilities such as Ming Ave (MNG), Coffee Rd(COF) and so on. On the 2nd Sheet I have the chart that show the miles.
example MNG to COF is 8.3 miles.

View 9 Replies View Related

Record Changes To Specific Cell

Dec 8, 2007

I'm working on a spreadsheet that has a counting number value on the cell L2 and a score on cell L4. These cells will be changed during the day, L2 always increasing, but not with a uniform increment. The interval between the changes also varies. Each time L2 changes, L4 may or not change. I'd like to keep the record of the changes on L2 and L4, each one in a row, together with the date and time when the changes where made to L2 and only add one new row if the L2 value has changed. The records should start from line 6 on...

See the attached file for more details. Currently I'm doing the updates to the record rows manually, I'd like to make it automatic every time the value of L2 is changed from the previous value to a new one.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$2" Then
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target * 2 does not _
put the code into a loop.
Application.EnableEvents = False............

View 5 Replies View Related

Function Search The Specific Record

Dec 21, 2006

im trying to do is create a database of autocad drawings based on style of house number of bedrooms sq ft and if theres a garage. what i am wondering is how can i add a search function. for example i want to find a Cape with 2 bedrooms...i have all the information on the spreadsheet. what do i need to do to be able to search among the spread sheet without using the find function this is just the beginning of the data

View 14 Replies View Related

Link A Txtbox Value To A Specific Record?

Aug 28, 2009

Basically, I have a large spreadsheet with basic data including names, dates and a unique identifier number (UIN). I have created a questionnaire in a userform (with a UIN txtbox) and want to link the answers to the specific individuals on the worksheet.

i.e. If UIN 201 fills in the questionnaire on the userform it will enter the details on the spreadsheet next to the details already entered for UIN 201.

View 14 Replies View Related

Keep A Record Or Log Of Every Value Input In A Specific Cell

Jul 17, 2008

I am trying to create a continuously updating record or log of the numbers in a particular cell that has numbers that change frequently. I want every number that was every in that cell recorded in a table or column so I can analyze those numbers. Please note that I do not have much VBA experience.

View 9 Replies View Related

Calculating Distances With Obstacles?

Dec 10, 2011

I am looking to calculate Manhattan (rectilinear) distances for location to location.

I have been able to calculate that with a formula in excel based on the X and Y coordinates.

The issue is that I have obstacles between the coordinates. Is there anyway to incorporate this into excel to avoid doing it manually.

View 2 Replies View Related

Record/write Macros Independent Of A Specific File

Oct 12, 2009

I have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.

Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file

View 13 Replies View Related

Lookup The Values Contained Within The Matrix To Get Distances Between Them

Oct 8, 2009

I'm updating my database of distances between different cities in our records... but the source that i have to lookup is actually a distance matrix....something like this...

From/ToMumbaiPuneDelhiKochiBanglore
Mumbai024014501800900
Pune240015501650700
Delhi14501550028002400
Kochi1800165028000500
Banglore90070024005000

Now i need to lookup the values contained within the matrix to get distances between them. I'm hoping if i can do that i dont have to actually manually sit and list down all the possible combinations of cities and distances between them but directly lookup the distances from this matrix.

View 2 Replies View Related

Now() As Record Id; What Record ID Schemes Are Fequently Employed Besides Date/time

May 25, 2007

I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.

I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.

Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................

View 2 Replies View Related

How To Calculate Mileage

Jun 23, 2014

trying to figure out a formula that would save me heaps of time trying to calculate mileage on expense reports each month, and I cannot for the life of me get the formula down. Following are the specifics:

Mileage is reimbursed at a rate of $0.17/mi for the first 198 miles in the week. Any mileage after 198 is calculated at a rate of $0.56/mi. The mileage log I have is a monthly log, and expenses are turned in on a monthly basis.

Is there a way to create a formula that will calculate on a weekly basis, at the correct rate based on the number of miles in that week thus far?

View 2 Replies View Related

Mileage Matrix

Mar 4, 2010

I am trying to figure out a better way to do my mileage for when I drive for work.

Currently I need to look at a sheet and see where I started and where I stopped and then I’ll see the distance.

Kinda look something like this.

Home
Work
School
Home
031Work
304School
140

What I would like to do is type in the “to” and “from” cell and have it automatically know the miles based on the chart above.
DateToFromDirectionTimeMiles3/4/10Home
Work

View 9 Replies View Related

Mileage Tariffs For A Taxi

Jul 17, 2009

I am trying to create a formula which will display tarriffs for a taxi depending on how far it has travelled -

Distance Tarriff
0-0.5 miles$1.00
0.6-2 miles$0.80
3-5 miles $0.75
6-20 miles$0.70

I want to enter the exact mileage travelled into a cell e.g. 8 miles, then I would like the exact cost to be shown in the cell below. In this case the cell will display $0.70 for 8 miles.

View 14 Replies View Related

Mileage Calculation Form

Jul 25, 2008

I'm working on creating a form where truck drivers can calculate their mileage to and from job sites. What I have now is two lists, one is a "to" list, where it lists the Job Sites, and one is a "from" list, where it lists the destinations (usually to Rock Quarries). These two lists are being referenced in the user form as combo boxes. An additional list holds the distances for each to and from possibility.

What I would like to do is when the user selects a to and from location from the combo boxes in the form, the form references the mileage distance from the list of to-from possibilities.

View 14 Replies View Related

GAS Mileage Chart Formatting

Feb 20, 2008

I have created a gas mileage chart where the MPG line will change from one color to another as the "type" of driving changes (Highway to City). However, the chart also ignores any non-full tank data to eliminate spikes that arise in your MPG calculations when you don't fill your tank up all the way.

I have attached a spreadsheet detailing what i have done and would like to know if anyone can think of an easier way to replicate this chart.

View 10 Replies View Related

Create Mileage Log From One Sheet To Another

Oct 26, 2008

I am trying to create a very simple mileage log that would get data from one sheet when I input it into another. (I attached the sheet). I have it set up so that it the formula is on bottom of sheet labeled store to store mileage but I would like to be able to track it easier on a different sheet called mileage log. So basically what I would like to do it put a formula in the mileage log so that I can just type in the store number in the to and from cells and have it populate it from the store to store mileage shet. I am an amateur. If it is easier I can just make another simple excel sheet that is on a seperate worksheet instead of using the mileage log.

View 4 Replies View Related

Flag Record That Has Overlapping Date As Another Record?

Mar 16, 2014

I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.

What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.

View 1 Replies View Related

VLookup: Calculate The Mileage From Two Destinations

Jul 9, 2009

I am trying to create a multiple v look up formula to calculate the mileage from two destinations. I have a table with a From column, To Column, and miles column with data.

I would like to automatically calculate the mileage determined upon what the user picks as the two destinations from a drop down list. I thought it would work like this =VLOOKUP(B5:C5,G3:I14,2) But I'm not sure if I can have a range as the lookup value. I have attached my sheet I am working from.

View 3 Replies View Related

Use Closest Date To Find The Starting Mileage Of Car?

Dec 26, 2012

I have a list with all our company cars (300). I also have a list with all the transactions of the fuel cards (12000 lines). Now I need the find for every company car (exact match) the nearest fuel-consumption-date (closest match to the given starting date) and take the mileage of that transaction. I've added an example. I have tried linking carnr and date (like carnr&"#"&date) and match these, but I don't always get the right car number.

View 1 Replies View Related

Calculate Pay For Mileage While Factoring In The Number Of Participants

Jun 2, 2009

I'm trying to do is calculate pay for mileage while factoring in the number of participants. In other words, if the mileage was between 0 and 50 miles, and there were 5 or less people, then this cell should be $35.00. But if it's between 0 and 50 and there were more than 5, then this cell should display 175/the number of people (175 being 35 * 5). In other words, more than five people would receive an equal amount of the total pay for five people (this is because five people are the standard, but there are instances in which six or seven could participate and I was hoping to avoid hardcoding that).

The rest of the if statements simply follow that same formula, but for different mileage/pay rates.

View 6 Replies View Related

Calculate Mileage For Discrete Date Ranges

Aug 8, 2008

I have a list of mileage rates that correspond with different date ranges. For example the mileage rate for March 19, 2008 – July 31, 2008 was 50.5 cents per mile. I have 11 different mileage rates / date ranges from 2000 onwards.

I would like to devise a way that I could input the date and the number of miles travelled into the spreadsheet, then Excel would check the date entered in order to choose the appropriate mileage rate and calculate the dollar amount based on the # of miles input.

Here are the date ranges and rates in the event that anyone is interested in putting something like this together. I would be grateful for an example for one or two of the rates though and hopefully could extrapolate from there.

1/1/00 - 3/31/00 - 32.5 cents per mile
4/1/01 - 12/31/01 - 34.5
1/1/02 - 12/31/02 - 36.5
1/1/03 - 12/31/03 - 36
1/1/04 - 12/31/04 - 37.5
1/1/05 - 8/31/05 - 40.5
9/1/05 - 12/31/05 - 48.5
1/1/06 - 1/31/07 - 44.5
2/1/07 - 3/18/08 - 48.5
3/19/08 - 7/31/08 - 50.5
8/1/08 - current - 58.5

View 5 Replies View Related

Auto Copy Template On Record Entry & Link Set Cells Back New Record Entry Sheet

May 26, 2009

I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.

1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)

2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.

3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab

View 6 Replies View Related

Macro To Print Record # To Record#

Mar 20, 2009

I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.

I have a spreadsheet that feeds from a master list in excel, from over 5000 records.

I need to print the s'sheet with any given indivdual record's information at any given time.

Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.

Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?

View 13 Replies View Related

Calculating Distances Between Back-to-back Locations

Aug 11, 2012

I have a layout something like the following:

A1
A2
A3

[Code]....

Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).

Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".

Ultimately I am looking to have a matrix which contains all the distances between each location:

A1
A2
A3
B1

[Code].....

View 4 Replies View Related

Record Macro Gives Message "Unable To Record"

Jan 6, 2007

Im trying to record a macro that will enter a formula in a cell each time I run the macro

Drop the Lowest2:

=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)

Drop Lowest1:

=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)

I know I can write the formula in a simpler way, but I was asked to make it like this.

The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded

View 14 Replies View Related

Getting Error "Unable To Record" When Record A Macro

Sep 1, 2009

This problem seemed to revolve around "digital signing" with further macro changes done from a different machine (without the proper certificate). Excel warns of the problem and then "unsigns" the project.

I've got a problem with spreadsheets that I've been working on. Now, when I tell it to record a macro, I get the macro name, shortcut assignment dialog box and then an error "Unable to record." After "Ok"ing that dialog box, the normal recording macro dialog comes up. Also, lo and behold, there will not be a macro recorded.

I've got no protected cells or sheets and it doesn't matter what security level is set. It is a "signed" macro but I can take off the signature and still have the problem. I checked for "Help - About" for deactivated modules and there was none.

View 2 Replies View Related

Add A Row Between Every Record

Dec 14, 2008

Is it possible to add a row between every record, that has different data for 2 columns ( compared to the row below )

View 14 Replies View Related

Looking Up And Returning More Than One Record

Nov 4, 2009

I am building a form that uses a combo box to lookup up a row in a table and return row data. To make things more complicated I want to return more than one record. For example: the table has a list of jersey. I search for a style jersey. I want to search the list and return all the colours for the chosen style. Sometimes there is only one colour or there can be up to 10. Have a look a the attached example.

View 2 Replies View Related

How To Group Record And Name

Jul 10, 2013

How to group different record and name the group. Attached file

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved