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

L

Legacy 69493

Guest
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:

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?

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Yogi Anand

MrExcel MVP
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?

L

Legacy 69493

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

L

Legacy 69493

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

L

Legacy 69493

Guest
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

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

Yogi Anand

MrExcel MVP
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

MrExcel MVP
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, ...

L

Legacy 69493

Guest
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

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

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

Replies
4
Views
346
Replies
3
Views
493
Replies
7
Views
320
Replies
7
Views
424
Replies
0
Views
226

1,181,055
Messages
5,927,863
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back