SOLVED

New Contributor

# Excel Conditional Referencing Problem

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

# Re: Excel Conditional Referencing Problem

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

# Re: Excel Conditional Referencing Problem

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

best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Excel Conditional Referencing Problem

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.

# Re: Excel Conditional Referencing Problem

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