complex (for me at least) referencing problem in timesheet.

[Legacy 69493]

Hey. Got one question. I made myself a spreadsheet with multiple sheets to keep track of my hours and checks at/from work. Everything is working well, except for:

I've got 1 column that adds up my hours for every 2 week period. The hours are listed on a different sheet. So, everytime the cell goes down 1, it is supposed to add the next 14 consecutive cells from another sheet. This is the working formula I have in there now:

Cell M6:
=SUM(Hours!C36:C49)-MAX(Hours!T36:T49,Hours!U36:U49)

(column C is for total hours. other stuff is for figuring overtime. irrelevant for my question.)

The problem is, the "36" and "49" had to be typed in my me manually. I want it to all be automated so I can just copy the formula down, and it will work.

I have written something that i thought would solve the problem:

=ADDRESS((((CELL("row",M6))-4)*14+8),3,1,TRUE,"Hours")

The above returns "Hours!$C$36"

However, I tried to copy the second formula, and use it to replace "Hours!C36" in the first formula. Every way I try to do this gives me an error. What is the proper way to do this replacement?

 

[Yogi Anand]

Hi bugmenot:

Cell M6:
=SUM(Hours!C36:C49)-MAX(Hours!T36:T49,Hours!U36:U49)

(column C is for total hours. other stuff is for figuring overtime. irrelevant for my question.)

The problem is, the "36" and "49" had to be typed in my me manually. I want it to all be automated so I can just copy the formula down, and it will work.
....
....
However, I tried to copy the second formula, and use it to replace "Hours!C36" in the first formula. Every way I try to do this gives me an error. What is the proper way to do this replacement?

Question for you ... Should the formula for cell M7 be ...

=SUM(Hours!C37:C50)-MAX(Hours!T37:T50,Hours!U37:U50)

if my assumption is not correct ... please post what should the formula be for cell M7?

 

[Legacy 69493]

M7

M7:
=SUM(Hours!C50:C63)-MAX(Hours!T50:T63,Hours!U50:U63)

M8:
=SUM(Hours!C64:C77)-MAX(Hours!T64:T77,Hours!U64:U77)

M9:
=SUM(Hours!C78:C91)-MAX(Hours!T78:T91,Hours!U78:U91)

etcetera.

 

[Legacy 69493]

I have a sheet where I list the hours I worked. It is vertical. it includes saturdays and sundays. It is called "Hours". On another sheet, I keep track of the checks I received. I like to predict how much I will get on the next check, and to know how far my prediction is off from the actual check. To know that, I need to know my total hours for every 14 day period. However, I don't want my check information to be 14 lines apart. That's why I can't use Excel's automatic relative positioning.

 

[Legacy 69493]

I will try to explain better.
M6: =SUM(Hours!C36:C49)-MAX(Hours!T36:T49,Hours!U36:U49)

Let's take the first part of that:

=SUM(Hours!C36:C49)

I want to replace the "Hours!C36" with "ADDRESS((((CELL("row",M6))-4)*14+8),3,1,TRUE,"Hours")"

The blue part returns "Hours!$C$36", just in a roundabout (copy and pasteable) way.

So, I would like to end up with

=SUM(ADDRESS((((CELL("row",M6))-4)*14+8),3,1,TRUE,"Hours"):C49)

Which, as best as I can figure, makes sense logically and mathematicaly, however Excel tells me it is an error.

 

[Yogi Anand]

I have a sheet where I list the hours I worked. It is vertical. it includes saturdays and sundays. It is called "Hours". On another sheet, I keep track of the checks I received. I like to predict how much I will get on the next check, and to know how far my prediction is off from the actual check. To know that, I need to know my total hours for every 14 day period. However, I don't want my check information to be 14 lines apart. That's why I can't use Excel's automatic relative positioning.

Hi bugmenot:

One way ... Use the following formula in cell M7:

=SUM(INDEX(Hours!C:C,(ROW(M6)-6)*14+36):INDEX(Hours!C:C,(ROW(M6)-6)*14+49))-MAX(INDEX(Hours!T:T,(ROW(M6)-6)*14+36):INDEX(Hours!T:T,(ROW(M6)-6)*14+49),INDEX(Hours!U:U,(ROW(M6)-6)*14+36):INDEX(Hours!U:U,(ROW(M6)-6)*14+49))

When you copy it down to M7, M8, ... it would adjust it automatically per your specification.

Let me know how it goes.

 

[Yogi Anand]

Or alternately, you may use the following formula in cell M6 ...

=SUM(INDEX(Hours!C:C,(ROWS($1:1)-1)*14+36):INDEX(Hours!C:C,(ROWS($1:1)-1)*14+49))-MAX(INDEX(Hours!T:T,(ROWS($1:1)-1)*14+36):INDEX(Hours!T:T,(ROW($1:1)-1)*14+49),INDEX(Hours!U:U,(ROWS($1:1)-1)*14+36):INDEX(Hours!U:U,(ROWS($1:1)-1)*14+49))

and copy it down to M7, M8, ...

 

[Legacy 69493]

Success!

Mr. Anand, you are officially my hero for the day. I used exactly what you wrote, and it worked excellently. I had another column that was for overtime and was a different formula with a similar problem, and I did some copying and pasting after staring at your formula for a few minutes, and that works excellently now as well. At first I didn't understand why it worked, I've never used the "Index" function, but I did some reading, and it makes sense now. I appreciate the time you spent to help me. thanks!

 

[Yogi Anand]

... just for the day ... and I was thinking :wink:

You Are Very Welcome bugmenot ... now let us keep EXCELing.