Help using OFFSET Function?

[petey wheatstraw]

I have a spreadsheet with two worksheets; the first titled "ROUTING SHEET" and the second titled "WEEKLY HOURS". Column A of ROUTING SHEET displays technician names (A4:A29), Column B displays technician number, Column C displays Work area and Column D displays Hours of work for the day. This range (B4:D29) is then repeated for the year with information adjusted daily. What I need to do is sum the working hours for each individual on a weekly basis (D4+G4+J4 etc.) (week 1 is range B4:V29) in WEEKLY HOURS. Can somebody tell me if this is possible and possibly how to go about creating this formula?

Also, I've tried to include an attachment but not sure if it worked properly

Thanks,
Greg

Working Hours.xlsx

[hamjam]

Hello Greg
You logic is correct, you can add formula on your WEEKLY HOURS for each week that adds up the hours per technician / row. Then you can copy the formula down the week, to obtain all the hours for all technicians.
You could do this for every week, for the corresponding cells in ROUTING SHEET

This becomes fair easy to maintain as if there is a problem, you just edit the formula and make sure it is extracting from the correct cells

so your formula for week11, in the 1st technician is :

Please Login or Register  to view this content.

You could also write a macro, but that might be less understandable if you are not familiar with code.

My suggestion is to run with the formula, it will take you 2 minutes to update every week for the new times and copy to the last person.

Regards

[petey wheatstraw]

Thanks for your help hamjam

I know it seems lazy on my part but I was hoping that there was a way to do this where I create one formula and just drag across horizontally and vertically. I thought that this is similar to something that I've done in the past but I guess it isn't. I'll adjust each column manually.

Thanks again,
Greg

[Pepe Le Mokko]

Maybe this could get you started for one week

Please Login or Register  to view this content.

and pull down as needed
Again, the layout of your source data makes it more difficult to extract a summary then plain columns would do ( which could be arranged in a nice table if needed)

[hamjam]

Welcome
I am sure smart contributors that have a way to skip 2 columns, the issue is copying the formula horizontally as the relative references skip 1 place for each column moved.
A macro would loop every 3 columns and each subsequent week, move to the start of the new week. But could go wrong if you decide to add a new column somewhere. That limits the maintainability.

Regards