SOLVED

Excel Conditional Referencing Problem

Copper Contributor

Hi,

 

I have what is essentially a daily time sheet for employee's. Each sheet is for a day of the week. In one row across the top, I have a series of codes in the format of x.xx.xx. In a column on the Y axis, I have a list of employee names. Essentially, each employee could have hours in any number of codes on the same day. 

 

What I am trying to do is sum all of the hours worked for each individual code throughout the week in a different sheet.

 

The problem is that these codes and their locations in that top row change on a weekly basis. Essentially, I need something that can test each cell in that row to see what the code is, and then sum all of the hours underneath it.

 

I have attached a sample file below. Any help would be appreciated.

 

Thanks!

4 Replies

@fiegl_ 

do you need to distinguish between RT, OT and DT, or do you just want to lump these together on the Total Hours sheet?

@Hans Vogelaar 

 

Just trying to lump it all together as a total hours worked for that specific code

best response confirmed by allyreckerman (Microsoft)
Solution

@fiegl_ 

 

In B2 on the Total Hours sheet:

 

=IFERROR(SUM(OFFSET(Monday!$E$27:$AB$27,0,MATCH(A2,Monday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Tuesday!$E$27:$AB$27,0,MATCH(A2,Tuesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Wednesday!$E$27:$AB$27,0,MATCH(A2,Wednesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Thursday!$E$27:$AB$27,0,MATCH(A2,Thursday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Friday!$E$27:$AB$27,0,MATCH(A2,Friday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Saturday!$E$27:$AB$27,0,MATCH(A2,Saturday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Sunday!$E$27:$AB$27,0,MATCH(A2,Sunday!$E$5:$AB$5,0)-1,1,3)),0)

 

Fill down to B6.

You are a gentleman and a scholar. That worked perfectly. Thanks for your expertise and help.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@fiegl_ 

 

In B2 on the Total Hours sheet:

 

=IFERROR(SUM(OFFSET(Monday!$E$27:$AB$27,0,MATCH(A2,Monday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Tuesday!$E$27:$AB$27,0,MATCH(A2,Tuesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Wednesday!$E$27:$AB$27,0,MATCH(A2,Wednesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Thursday!$E$27:$AB$27,0,MATCH(A2,Thursday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Friday!$E$27:$AB$27,0,MATCH(A2,Friday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Saturday!$E$27:$AB$27,0,MATCH(A2,Saturday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Sunday!$E$27:$AB$27,0,MATCH(A2,Sunday!$E$5:$AB$5,0)-1,1,3)),0)

 

Fill down to B6.

View solution in original post