SOLVED

Excel Conditional Referencing Problem

%3CLINGO-SUB%20id%3D%22lingo-sub-2430536%22%20slang%3D%22en-US%22%3EExcel%20Conditional%20Referencing%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2430536%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20what%20is%20essentially%20a%20daily%20time%20sheet%20for%20employee's.%20Each%20sheet%20is%20for%20a%20day%20of%20the%20week.%20In%20one%20row%20across%20the%20top%2C%20I%20have%20a%20series%20of%20codes%20in%20the%20format%20of%20x.xx.xx.%20In%20a%20column%20on%20the%20Y%20axis%2C%20I%20have%20a%20list%20of%20employee%20names.%20Essentially%2C%20each%20employee%20could%20have%20hours%20in%20any%20number%20of%20codes%20on%20the%20same%20day.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20do%20is%20sum%20all%20of%20the%20hours%20worked%20for%20each%20individual%20code%20throughout%20the%20week%20in%20a%20different%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20these%20codes%20and%20their%20locations%20in%20that%20top%20row%20change%20on%20a%20weekly%20basis.%20Essentially%2C%20I%20need%20something%20that%20can%20test%20each%20cell%20in%20that%20row%20to%20see%20what%20the%20code%20is%2C%20and%20then%20sum%20all%20of%20the%20hours%20underneath%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sample%20file%20below.%20Any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2430536%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2432190%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Referencing%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2432190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074669%22%20target%3D%22_blank%22%3E%40fiegl_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Edo%20you%20need%20to%20distinguish%20between%20RT%2C%20OT%20and%20DT%2C%20or%20do%20you%20just%20want%20to%20lump%20these%20together%20on%20the%20Total%20Hours%20sheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2432230%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Referencing%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2432230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20trying%20to%20lump%20it%20all%20together%20as%20a%20total%20hours%20worked%20for%20that%20specific%20code%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2432326%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Conditional%20Referencing%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2432326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1074669%22%20target%3D%22_blank%22%3E%40fiegl_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B2%20on%20the%20Total%20Hours%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(SUM(OFFSET(Monday!%24E%2427%3A%24AB%2427%2C0%2CMATCH(A2%2CMonday!%24E%245%3A%24AB%245%2C0)-1%2C1%2C3))%2C0)%2BIFERROR(SUM(OFFSET(Tuesday!%24E%2427%3A%24AB%2427%2C0%2CMATCH(A2%2CTuesday!%24E%245%3A%24AB%245%2C0)-1%2C1%2C3))%2C0)%2BIFERROR(SUM(OFFSET(Wednesday!%24E%2427%3A%24AB%2427%2C0%2CMATCH(A2%2CWednesday!%24E%245%3A%24AB%245%2C0)-1%2C1%2C3))%2C0)%2BIFERROR(SUM(OFFSET(Thursday!%24E%2427%3A%24AB%2427%2C0%2CMATCH(A2%2CThursday!%24E%245%3A%24AB%245%2C0)-1%2C1%2C3))%2C0)%2BIFERROR(SUM(OFFSET(Friday!%24E%2427%3A%24AB%2427%2C0%2CMATCH(A2%2CFriday!%24E%245%3A%24AB%245%2C0)-1%2C1%2C3))%2C0)%2BIFERROR(SUM(OFFSET(Saturday!%24E%2427%3A%24AB%2427%2C0%2CMATCH(A2%2CSaturday!%24E%245%3A%24AB%245%2C0)-1%2C1%2C3))%2C0)%2BIFERROR(SUM(OFFSET(Sunday!%24E%2427%3A%24AB%2427%2C0%2CMATCH(A2%2CSunday!%24E%245%3A%24AB%245%2C0)-1%2C1%2C3))%2C0)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20B6.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.