Applying a value to different employee

[Rhodestransit]

Im not sure if this will make sense
I have drivers that driver their normal bus runs, each run has an agreed amount of time the run takes to complete, each driver is on a different pay rate depending on years of service, sometimes a driver may work another run.
I want to know if I can have the sheet check for each drivers name and where it appears on the sheet, then apply the relevant numbers of hours to that driver.
Example c3 has a value of 3 hours so what ever name is in that cell they would be paid 3 hours.
we have around 600 cells each with a different value, and 200 employees that could be in any of those cells.
I am really stuck on this one.

 

[bryonwoods30]

so does c2 have the driver name, and c3 has the hours and you want the sheet to sum up all the hours of each driver?

 

[Rhodestransit]

Sorry i should have been more clear,
Each cell has a drop down list of all drivers to select who you want on that bus run for the day.
C2 has the drop down there are no cells with a value yet, I was planning on having a separate sheet to total all this up for each driver.
The main sheet is intended as a dispatchers screen to only see who is driving which bus.

 

[bryonwoods30]

you said you have 600 cells and only 200 drivers. so is the same drive in multiple cells for the same day?

 

[Rhodestransit]

Yes you could have the same driver on all 3 am school runs, High school, Elementary, Kindergarten.
But there may be a time where a driver only does one and someone else does the other 2 and they are on a different pay rate.

 

[bryonwoods30]

Ok
so if your drivers names are in column c, and the hours they drive are in column d on sheet1, then on sheet2 do this.

in column a put their names
then in cell b1 use the formula
=SUMIF(Sheet1!C:C,Sheet2!A1,Sheet1!D:D)

and just drag it down, it will sum up all the hours of the drivers. make sure the drives names are entered exactly the same as on sheet1

 

[Rhodestransit]

That works great! thank you so much.

 

[Rhodestransit]

We sometimes have a matron on the bus, can I link them to the same hours cell
Ie matron name in column e and use the same hours in column d ?

 

[bryonwoods30]

if you want to add matrons like that. Just have their names also somewhere on sheet 2 the same as you did the drivers. if you put the matrons names in column e on sheet1 then instead of your formula being

=SUMIF(Sheet1!C:C,Sheet2!A1,Sheet1!D:D)

it will be

=SUMIF(Sheet1!e:e,Sheet2!A1,Sheet1!D:D)

the sheet2!A1 needs to change to the cell where you have your matons names.

if you list the matrons in the same column as the drivers on sheet 2 then all you need to change in the formula is the sheet1!C:C to sheet!e:e for their names

 

[Rhodestransit]

Excellent! thanks again.