Copying data and pasting to certain sections of a table depending on what conditions are met

FNQJedi

New Member
Joined
May 14, 2019
Messages
2
Hi all,
Fairly new to this. I've got a sheet where calculations are done, and a table where the results of those calculations are copied. A simple example would be that I have 5 trucks that work everyday for a week. Using the calulations sheet, I would enter the truck ID number in a cell, and the date the truck worked in another cell, then calculate the kilometers the truck has travelled for the day. I then want to be able to press a button to transfer the kilometers to the correct spot on the table depending on what truck ID number and date was entered on the calulator. I can assign macros to buttons and stuff but I'm very new to VBA. Otherwise if theres a formula that works please let me know? Any help is greatly appreciated.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,555
Office Version
  1. 2013
Platform
  1. Windows
Give us a example of where you plan to enter the truck Number and Date

Like say:
Sheet named Master Range("A1") I will enter Truck Number
Sheet named Master Range("B1") I will enter Date

Now you said:
then calculate the kilometers the truck has travelled for the day

How do you plan to do this?

I would think you would need to enter Start mileage in

Sheet named Master Range("C1")
And End of trip mileage in

Sheet named Master Range("D1")

Still not sure what you mean when you say:


Then We search a Table you did not say the name of the Table or where on the Table we might fine the Truck number

See we always need details like this:
<strike>
</strike>
 

FNQJedi

New Member
Joined
May 14, 2019
Messages
2
Sorry for the confusion. I will try again using the actual problem I am working on.

I have up to 8 trucks running between 1-12 farms every day. I am trying to record the total number of kilometers and hours all the trucks have spent travelling to each farm each day for a seven day period. The trucks record their start and end milage whenever they change to a new farm, and the total hours for each farm too.

On sheet named Gr2("M17") I will enter farm ID number.
Sheet named Gr2("P17") I will enter the Date the trucks worked.
On Sheet Gr2("L19:L26") I have listed my 8 trucks in ascending order, 1-8.
On Sheet Gr2("M19:M26") I have entered the corresponding hours the trucks have recorded for travelling to that farm.
On Sheet Gr2("N19:N26") I have entered the corresponding start mileage of each truck.
On Sheet Gr2("O19:O26") I have entered the corresponding end mileage of each truck.
On Sheet Gr2("P19:P26") a formula shows the corresponding kilometers travelled for each truck.
On Sheet Gr2("M27") the total hours of all the trucks is displayed. This is one of two figures I would like to record in a seperate table.
On Sheet Gr2("P27") the total kms for all the trucks is displayed. This is the second figure I would like to record in a seperate table.

Once I have these two figures I'd like to be able to click a button and transfer them to the correct spot in Table1 which is on the same sheet.

Table1 has 10 columns of data with the headers on row 2 of the sheet. The 12 farms are listed in a column seven times for each day of the week next to the corresponding dates.

Sheet Gr2("A2") contains the header "Date", and starting in the cell directly underneath has the first date listed 12 times (one for each depot), then the second date listed 12 times, and so on for all 7 dates during the week.
Sheet Gr2("B2") contains the header "Farm", and starting in the cell directly underneath has all 12 farm ID numbers listed 7 times next to their corresponding date.
Sheet Gr2("C2") contains the header "Worked Hours" and this is the column I would like to have the total hours ("M27") that have been calculated transferred to. It will have to go in the correct cell based off the matching date and farm ID number.
Sheet Gr2("J2") contains the header "Kms" and is the column I would like to transfer the calculated total kms ("P27") to. Again, it will have to go in the correct cell based off the matching date and farm ID number.

There are other columns as well in this table containing other information but I do not believe they are related to what I'm doing here.

Ideally I would be able to put seperate buttons on sheet Gr2("N27") and Gr2("Q27") which could transfer either the hours information or the kms information respectively. I hope this explains what I'm trying to do.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,314
Messages
5,635,506
Members
416,861
Latest member
Breadnjam18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top