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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Job[/TD]
[TD]shift hours[/TD]
[TD]mon[/TD]
[TD]tue[/TD]
[TD]wed[/TD]
[TD]thur[/TD]
[TD]fri[/TD]
[/TR]
[TR]
[TD]job 1 [/TD]
[TD]0930-1800[/TD]
[TD]sally[/TD]
[TD]bill[/TD]
[TD]tom[/TD]
[TD]bob[/TD]
[TD]sally[/TD]
[/TR]
[TR]
[TD]job 2 [/TD]
[TD]0730-1600[/TD]
[TD]bill[/TD]
[TD]sally[/TD]
[TD]bob[/TD]
[TD]pete[/TD]
[TD]bob[/TD]
[/TR]
[TR]
[TD]job 3 [/TD]
[TD]0800-1630[/TD]
[TD]tom[/TD]
[TD]bob[/TD]
[TD]sally[/TD]
[TD]tom[/TD]
[TD]pete[/TD]
[/TR]
[TR]
[TD]job 4
[/TD]
[TD]0900-1800[/TD]
[TD]pete[/TD]
[TD]tom[/TD]
[TD]pete[/TD]
[TD]pete[/TD]
[TD]tom[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]staff[/TD]
[TD]mon[/TD]
[TD]tue[/TD]
[TD]wed[/TD]
[TD]thur[/TD]
[TD]fri[/TD]
[/TR]
[TR]
[TD]sally[/TD]
[TD]0930-1800[/TD]
[TD]0730-1600[/TD]
[TD]0800-1630[/TD]
[TD][/TD]
[TD]0930-1800[/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]0730-1600[/TD]
[TD]0930-1800[/TD]
[TD]sick[/TD]
[TD]sick[/TD]
[TD]sick[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD]0800-1630[/TD]
[TD]0900-1800[/TD]
[TD]0930-1800[/TD]
[TD]0800-1630[/TD]
[TD]0900-1800[/TD]
[/TR]
[TR]
[TD]pete[/TD]
[TD]0900-1800[/TD]
[TD]arl[/TD]
[TD]0900-1800[/TD]
[TD]0900-1800[/TD]
[TD]0800-1630[/TD]
[/TR]
[TR]
[TD]bob[/TD]
[TD][/TD]
[TD]0800-1630[/TD]
[TD]0730-1600[/TD]
[TD]0930-1800[/TD]
[TD]0730-1600[/TD]
[/TR]
</tbody>[/TABLE]

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 ?