Roster spreadsheet using v/h lookup, sumproduct.sumif....??

[Arafruafrog]

Hi Forum

I am trying to simplify a roster spreadsheet for payroll.

Sheet 1 Roster - job, staff and days working

Job	shift hours	mon	tue	wed	thur	fri
job 1	0930-1800	sally	bill	tom	bob	sally
job 2	0730-1600	bill	sally	bob	pete	bob
job 3	0800-1630	tom	bob	sally	tom	pete
job 4	0900-1800	pete	tom	pete	pete	tom

<tbody>
</tbody>

What I am trying to do is automatically populate the payroll spreadsheet with the data from the roster eg: hours worked by each staff member.
I have tried a v and hlookup table with job and hours and then a sumif to look for staff member in the roster week but can not get my logic correct...

I want the payroll data to update automatically if I change a staff member in the roster

Sheet2 for payroll

staff	mon	tue	wed	thur	fri
sally	0930-1800	0730-1600	0800-1630		0930-1800
bill	0730-1600	0930-1800	sick	sick	sick
tom	0800-1630	0900-1800	0930-1800	0800-1630	0900-1800
pete	0900-1800	arl	0900-1800	0900-1800	0800-1630
bob		0800-1630	0730-1600	0930-1800	0730-1600

<tbody>
</tbody>

Many thanks for any assistance from the excel gods....
Tee

 

[Gerald Higgins]

This is probably do-able.

Question - how does sheet 2 know that Bill was sick on Wednesday ?
I can't see that data in sheet 1.

 

[Arafruafrog]

Hi Gerald Thanks for your response.
No I am not sure how I can enter this - I was just going to copy the payroll data and paste it into another sheet and then manually correct the arl and sick days...
Cheers Tee

 

[Gerald Higgins]

OK let's park the sick and arl days for now.

Let's make some assumptions about how your data is laid out - adapt as required.
1) Your input data is on SHEET1, exactly as laid out in your OP, in the range A1:G5, with the the word "Job" in A1, and the entry for TOM on Friday on job 4 in cell G5.
2) Your output report is on SHEET2, again exactly as laid out in your OP, in the range A1:F6, with the word "Staff" in cell A1, and the entry for 0730-1600 for Bob on Friday in cell F6.

In cell B2 on SHEET2 input this formula

=INDEX(SHEET1!$B$2:$B$5,MATCH(SHEET2!$A2,OFFSET(SHEET1!$A$1,1,MATCH(SHEET2!B$1,SHEET1!$A$1:$G$1,0)-1,5,1),0))

and copy to the range B2:F6.

This formula is not perfect, for several reasons, including . . .
1) if someone does not work on a particular day (such as Bill on Wed/Thu/Fri) it returns #N/A - there are various ways of dealing with this, including wrapping the whole thing in an IFERROR statement or similar
2) it does not deal with sick / arl days.

 

[Arafruafrog]

Excellent many thanks for that - I got it is work for some cells but not others...not sure why! I have copied it into the other cells.
Can I do an IFERROR that has a message included something like "Check"?
Cheers Tee.

 

[Gerald Higgins]

Can you do an IFERROR . . . ?

Sure, go right ahead.

If you need more help with this, please explain exactly what further help you need as it's not clear to me at the moment.

 

[Arafruafrog]

Hi Gerald

Appreciate your help I am still having trouble - keeping looking at the formula but can not see what is wrong.
It is working for some and not others as you will see in sheet S2.

Here is a link you should be able to download it. https://drive.google.com/file/d/1zoc-hSQX1EMDrFSMzRcZvbFii7tcbS0n/view?usp=sharing

Many thanks again Tee,

 

[Arafruafrog]

just am update through perseverance I finally got it to work.

 

[Gerald Higgins]

Just out of curiosity, what did you need to do to get it to work ?