Forum Discussion

Copper Contributor

Jun 09, 2021

Solved

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...

EXCEL HELP.xlsx760 KB

Formulas and Functions

Macros and VBA

HansVogelaar
Jun 09, 2021
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.

HansVogelaar

MVP

Jun 09, 2021

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?

fiegl_

Copper Contributor

Jun 09, 2021

HansVogelaar

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

HansVogelaar
MVP
Jun 09, 2021
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.
- fiegl_
  Copper Contributor
  Jun 09, 2021
  You are a gentleman and a scholar. That worked perfectly. Thanks for your expertise and help.