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.
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.
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?
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
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.
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.
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.
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.
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.
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.
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.
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.
I'm missing something in my UserForm initialization code. If I fill the form out once and click 'OK' (run the code to put the form data into a sheet), when I go back into the form all the old info is still there. If I then click 'Cancel' (Unload Me) and reopen the form, the old data is cleared out. What am I missing to make it clear it out the first time?
I'm having trouble trying to come up with a way to insert data fields into a spreadsheet form. I have a travel authorization form that I would like to have automatically fill in the required fields based on typing in a name. i.e. I would type in an employees name, and it would automatically fill-in the correct address, etc for that employee. I have attached a spreadsheet that contains one sheet as the form, and another sheet containing the employee data. I know nothing about VBA, but I have a feeling that is where I need to go.
I am needing to create a form that exports data (a quote) to an Excel Db (table) and is then able to recall the data back into the form. (the default form in excel does this and I want to copy that.)
Once the data is called back in, I can then export it to another Table to show that the quote has been approved and will be used.
I am having trouble with the VBA coding that copies the inputted quote in Cell C2 (the reference for the quote number) of the "Form" sheet and looks it up in the "Database" sheet. I have tried several variations of code, but nothing works so far.
-SS Sub RecallQuote() ' ' RecallQuote Macro ' Sheets("Form").Select Range("C2").Select 'this is the cell that holds the quote number to look up from the table
I have a spreadsheet I use to keep track of weekly sales patterns and use for estimating the amount of a product I would need to order taking into account what I would expect to sell in a given week and what stock I have at present. On the example I've attached, I show where I enter my storeroom count figures, which are organised by supplier and the position in which a particular product appears on the supplier's order form. I have a page which lists the orders by suppliers and which are used to place the orders by e-mail or telephone.
At present I have each supplier section of the order form directly linked to a cell on the storeroom count as per columns K to M on the attached file. However, this means that as products are de-listed by suppliers and extra products become available, I have to edit the formula in each cell as the products now appear in a different position on the storeroom count and may otherwise end up on the order form for a different supplier. I would like to set it up so that I just have to select the supplier name and the table below will automatically fill with the required info, in order of the position they appear on the supplier's form. I'm struggling to combine vlookup and hlookup. Is there a way to do it or do I need to rethink?
I'm trying to pull up a second form from a command button within a form. There's a command button in a sheet to open the first form (frmOrderInput.) Then there's another command button in that form to open the second form (frmPriceInput.)
I have got a userform that fits my 24inch monitor screen perfectly, however it doesn't fit other screens. How do I get windows style scrollbars added to the form so people can use these to see the whole form?
I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:
When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.
Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.
I have two forms...clicking a specific button on the first form should display the second form which works fine.
VB: Private Sub Image_Employees_Click() Form_Main.Hide Form_Employees.Show
[Code].....
When I do this twice, however, the second form seems to freeze/hang and I have to close Excel and then re-open it. I feel like I'm overlooking something obvious...
I have created a registration workbook for this year's youth sports league. All of the information is entered into a User Form and separated onto it's appropriate sheet designated by the child's age. Next year, I would like to use this year's workbook to look up returning players.
Will it be possible to add a "lookup" button into my form, or create a lookup program, that once the registrar clicks on the correct player, the information is plugged into the User Form, the registrar adjusts the age and any necessary info, presses enter, and the information is copied into the appropriate category in the new workbook? I haven't worked with User Forms long enough to know if they can be filled in that way, but if this can be done, you are the people who would know.
I'm using a series of user forms for data entry to a workbook, some of them open next stage user form on completion (OK cmd button).
All that is working fine, but I'd like the initial form to close on showing the next one. I've tried adding Unload and Hide commands following the frmInsertEntry.Show (Next stage form), to no avail, but I'd like this user form to close or hide at the end of the sub.
Private Sub cmdContinueType_Click()
ActiveWorkbook.Sheets("Records").Activate 'Select starting cell in record sheet Range("N3").Select
Do If IsEmpty(ActiveCell) = False Then ' Search for next empty cell ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True
If optDrillType = True Then frmDrillEntry.Show Else frmInsertEntry.Show End If
I have created a form that pops up using a macro when the form is opened. It is a simple form that uses optionbuttons in a group to select Intl or Dom.
Problems:
1) How do you get the form to close once a button is selected?
2) The user can close the form without selecting a button (the X at the top).
3) How do you get the data selected onto the excel spreadsheet?
I need to create a form that if you were to enter a name in the text box it would fill all the information across that row into other boxes on the form. Basicly if I type John Doe in the text box it would look in col A for John Doe and then put whats in the cells on that row into different boxes on my form. I have tried searching but I have had no luck. Im very new to forms so this is a great experience.
I have 2 forms. Form1 and Form2. I load Form1 to display. A button on Form1 calls Form2 to display. After I hit Cancel-Unload on Form2 it doesn't dissapear. How do I get rid of the residual image of pesky Form2 after I unload it and only display Form1 WITHOUT having to reload Form1 after Form1 calls Form2
In English Form1 Displays Form1 call Form2 to display Form2 is unloaded by hitting a button on Form2 Form2 is unloaded, however, it's image still displays!!!
Need some way to force screen to refresh without having to unload Form1!
I have 2 different forms that I need info from one, added to the other.
The reason for this is to update pricing from a new file, into an older file with the same product code for each product.
on form 1(the one I want to keep), column x is price(that I want to update from form 2 column L), and column B is the product code(sku)
Now on form 2 Column L is the The customer price(this is the data I need moved over to column X on form 1. and column I is the UPC 10(sku) that needs to match the same sku(product code) on form 1.
Gee this sounds confusing aFTER i TYPED IT.. i HOPE THIS MAKES SENSE. i WILL ALSO ADD THE 2 FILES, SO YOU CAN SEE WEHAT I am talking about.
Please help as I have about 30,000 items total, and would take way too long to update prices manually every 2 - 3 months.
I have a form with Option Buttons and once a selection has been made it opens the relevant sheet on the workbook but I can get it to open the relevant form to feed that worksheet once the selection has been made from the opening form. Is it possible to do this and if so dose anyone no the code.
