Excel formula does not copy over

**cruzio** · 09-07-2007, 11:44 AM

I have created a timesheet in Excel, with meal breaks, end times and total hours worked for a two week period. The formula =SUM(INT(X10)+Z10) calculates correctely in day 1 of week 1 and through the first week. When I copy the formula to week 2, it does not calculate. I have even entered the formula again, but it does not work. I even tried putting it on a new sheet. I also tried doing a paste special. I need help to complete this timesheet.

**NBVC** · 09-07-2007, 11:49 AM

What do you mean by "it does not calculate" are you getting wrong values or are you getting error message.?

**cruzio** · 09-07-2007, 11:51 AM

I am getting a total of 0.00 each cell the forumula is copied to has the correct range of cells, and the same formula, but it does not calculate the total hours. A friend in LA who is an expert worked on it for 2 hours, redoing by hand, and it did not work for him either.

**NBVC** · 09-07-2007, 11:52 AM

Is it possible to post a zipped version here for us to see?

**cruzio** · 09-07-2007, 11:57 AM

here it is. as you can see, if you change the end time, the total hours change in the first week. nothing happens at all in the second week.

**NBVC** · 09-07-2007, 12:06 PM

That's because there is is nothing in X16 and Z16 and cells below.

If you unhide those columns...you'll see formulas in X and Z corresponding to your first week data...but no formulas in the 2nd week....

You need to place formulas in X16 and Z16 and copy down.

**NBVC** · 09-07-2007, 12:08 PM

It also looks like you need data in Columns U, V and W (2nd week) to correspond to the formulas in X, Y and Z.

**daddylonglegs** · 09-07-2007, 12:12 PM

You're talking about P16:P21 right?

The formula in P16 is using data in hidden cells in columns X and Z, these, in turn are based on formulas in columns T, U, V and Y. But all these columns are blank for rows 16 to 21.

If you unhide the hidden columns and copy the formulas down for these columns then your formulas in P16:P21 should work.

Having said that, this is a horrendously complicated way to do this. some of the hidden formulas, especially in column X, are way more complicated than you need. As you're already beginning to find out, badly designed spreadsheets are very time-consuming to maintain. I suggest a re-design

**cruzio** · 09-07-2007, 12:18 PM

I did that and now I get a -8.00 in every cell for total hours in the second week...! the formula looks the same as before. perhaps I copied the wrong one in the hidden cell? which cell should I be copying the formula from? thanks!

**NBVC** · 09-07-2007, 12:21 PM

Originally Posted by cruzio

I did that and now I get a -8.00 in every cell for total hours in the second week...! the formula looks the same as before. perhaps I copied the wrong one in the hidden cell? which cell should I be copying the formula from? thanks!

You need to place data in T, U and V also since the other formulas also refer to these cells..... It may be a matter of straight copying from Week 1 data, but I don't know if the data should be the same...only you know..

**daddylonglegs** · 09-07-2007, 12:21 PM

You really don't need all those hidden columns...... Just an example, doesn't take into account K and L but, based on the data shown, you can get the same results for column P, with no hidden columns, just referencing the start, finish and break times direct, in P10 copied down

=(N10+F10-D10-G10)*24

**cruzio** · 09-07-2007, 12:40 PM

I am getting excited...I copied the forumula down, and now I have correct totals in M, T, THURS but not in WED, FRI...?? those show -7.50 I will check out T-W....I know this is a long way to do it, but someone helped me...I did not have a simple formula to compute time start, with lunch break, and end time to get daily totals.

**cruzio** · 09-07-2007, 12:47 PM

thank you all so much! I had not copied data in columns Q and R...when I did, calculations were perfect. You are a great support group...have a wonderful Friday...