Newbie requires Help

[McKfodder]

Good Morning
I have some knowledge on excel and but what I do know is a little out of date.
So I have been asked to build a Spread sheet for rostering purposes and what we are currently using seems so out of date even to me.

we have jobs which need covering ever day plus peoples rest days each job has a separate turn number and start and finish time.

What I would like to be able to do is type the job number in a cell lets say a1
then it fills in B1 C1 D1 with a start time, finish time and total hours worked.

I Have created a sheet with these all in a list but I do not know how to transfer the information onto another sheet. any help in this would be very gratefully recieved

 

[friel300]

what does the data where the info is pulled from look like?

 

[McKfodder]

Turn	Start Time	Finish Time	Job Length
COVER	03:55	13:38	09:43
NN951	03:56	12:26	08:30
NN952	04:29	11:21	06:52
NN953	04:57	13:00	08:03
NN954	04:46	13:49	09:03
NN955	05:26	13:56	08:30
NN956	05:57	14:26	08:29
NN957	05:57	13:21	07:24
NN958	05:59	13:26	07:27
NN960	06:41	15:25	08:44
NN961	06:31	14:21	07:50
NN962	12:03	20:08	08:05
NN963	12:57	20:13	07:16
NN964	14:31	22:35	08:04
NN965	13:31	22:27	08:56
NN966	13:46	22:32	08:46
NN967	14:03	23:05	09:02
NN968	17:38	02:24	08:46
NN969	14:48	00:01	09:13
NN970	16:06	00:57	08:51
NN971	15:32	01:01	09:29
NN972	15:26	23:08	07:42
NN973	14:06	23:08	09:02
NN974	18:08	01:47	07:39
NN975	13:08	21:37	08:29
NN976	06:19	15:25	09:06
NN977	04:30	14:00	09:30
NN978	13:00	22:30	09:30
Rest Day

<colgroup><col width="116" style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <tbody> </tbody>

 

[McKfodder]

	Name	Week Commencing		SUNDAY
	TURN	ON	OFF	Total
1			L
2			E
3			L

<colgroup><col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="350" style="width: 263pt; mso-width-source: userset; mso-width-alt: 12800;"> <col width="176" style="width: 132pt; mso-width-source: userset; mso-width-alt: 6436;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="167" style="width: 125pt; mso-width-source: userset; mso-width-alt: 6107;" span="4"> <tbody> </tbody>

 

[McKfodder]

what I would like to happen is to type the turn number in the box then it fills in the rest automatically is that possible

 

[McKfodder]

on the second sheet

 

[friel300]

you could try using Vlookup

the example I have below is your Hours Data on a sheet called Hours

=IF($A3="","",VLOOKUP($A3,Hours!$A:$D,2)
=IF($A3="","",VLOOKUP($A3,Hours!$A:$D,3)
=IF($A3="","",VLOOKUP($A3,Hours!$A:$D,4)

put those in cells B,C,D respectivly to show the Times & Total.

hope this helps

 

[McKfodder]

worked a treat thank you so much