I have one tab in a spreadsheet summarizing information collected in other groups of cells in another tab in the same spreadsheet. I would like to be able to copy down the formula in one tab to reference non-adjacent cells in the other tab, but I cannot figure out how to make each consecutive line reference the right cells in the other spreadsheet because there are gabs in between.
I need to be able to continue dragging down from O12 with formula ='Time Entry'!T22 so that O13 will ='Time Entry'!T30. Essentially with every line I drag the formula down in column O, it references the 8th next cell down in the Time Entry tab. I started by manually having to adjust the formulas but that's going to take forever when I have 52 rows to do this in.
Did that make sense?
I need to be able to continue dragging down from O12 with formula ='Time Entry'!T22 so that O13 will ='Time Entry'!T30. Essentially with every line I drag the formula down in column O, it references the 8th next cell down in the Time Entry tab. I started by manually having to adjust the formulas but that's going to take forever when I have 52 rows to do this in.
Did that make sense?
| Timecard Template (Revising).xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| O | P | Q | R | S | T | |||
| 10 | 0.00 | $0.00 | 0.00 | $0.00 | 0.00 | $0.00 | ||
| 11 | 0.00 | $0.00 | 0.00 | $0.00 | 0.00 | $0.00 | ||
| 12 | 0.00 | $0.00 | 0.00 | $0.00 | 0.00 | $0.00 | ||
| 13 | ||||||||
| 14 | ||||||||
| 15 | ||||||||
| 16 | ||||||||
| 17 | ||||||||
| 18 | ||||||||
| 19 | ||||||||
| 20 | ||||||||
| 21 | ||||||||
| 22 | ||||||||
| 23 | ||||||||
| 24 | ||||||||
| 25 | ||||||||
| 26 | ||||||||
| 27 | ||||||||
| 28 | ||||||||
| 29 | ||||||||
| 30 | ||||||||
| 31 | ||||||||
| 32 | ||||||||
| 33 | ||||||||
| 34 | ||||||||
| 35 | ||||||||
| 36 | ||||||||
| 37 | ||||||||
| 38 | ||||||||
| 39 | ||||||||
| 40 | ||||||||
| 41 | ||||||||
| 42 | ||||||||
| 43 | ||||||||
| 44 | ||||||||
| 45 | ||||||||
| 46 | ||||||||
| 47 | ||||||||
| 48 | ||||||||
| 49 | ||||||||
| 50 | ||||||||
| 51 | ||||||||
| 52 | ||||||||
| 53 | ||||||||
| 54 | ||||||||
| 55 | ||||||||
| 56 | ||||||||
| 57 | ||||||||
| 58 | ||||||||
| 59 | ||||||||
| 60 | ||||||||
| 61 | ||||||||
Schedule - Summary | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O10:P10 | O10 | ='Time Entry'!T6 |
| Q10:R10 | Q10 | ='Time Entry'!T7 |
| S10:T12 | S10 | =SUM(O10,Q10) |
| O11:P11 | O11 | ='Time Entry'!T14 |
| Q11:R11 | Q11 | ='Time Entry'!T15 |
| O12:P12 | O12 | ='Time Entry'!T22 |
| Q12:R12 | Q12 | ='Time Entry'!T23 |
| Timecard Template (Revising).xlsx | |||||
|---|---|---|---|---|---|
| S | T | U | |||
| 5 | Weekly | Hours | Pay | ||
| 6 | Reg | 0.00 | $0.00 | ||
| 7 | OT | 0.00 | $0.00 | ||
| 8 | SUB TOT | 0.00 | $0.00 | ||
| 9 | Holiday | 0.00 | $0.00 | ||
| 10 | Total | 0.00 | $0.00 | ||
| 11 | P/Diem | $0.00 | |||
| 12 | |||||
| 13 | Weekly | Hours | Pay | ||
| 14 | Reg | 0.00 | $0.00 | ||
| 15 | OT | 0.00 | $0.00 | ||
| 16 | SUB TOT | 0.00 | $0.00 | ||
| 17 | Holiday | 0.00 | $0.00 | ||
| 18 | Total | 0.00 | $0.00 | ||
| 19 | P/Diem | $0.00 | |||
| 20 | |||||
| 21 | Weekly | Hours | Pay | ||
| 22 | Reg | 0.00 | $0.00 | ||
| 23 | OT | 0.00 | $0.00 | ||
| 24 | SUB TOT | 0.00 | $0.00 | ||
| 25 | Holiday | 0.00 | $0.00 | ||
| 26 | Total | 0.00 | $0.00 | ||
| 27 | P/Diem | $0.00 | |||
| 28 | |||||
| 29 | Weekly | Hours | Pay | ||
| 30 | Reg | 0.00 | $0.00 | ||
| 31 | OT | 0.00 | $0.00 | ||
| 32 | SUB TOT | 0.00 | $0.00 | ||
| 33 | Holiday | 0.00 | $0.00 | ||
| 34 | Total | 0.00 | $0.00 | ||
| 35 | P/Diem | $0.00 | |||
| 36 | |||||
| 37 | Weekly | Hours | Pay | ||
| 38 | Reg | 0.00 | $0.00 | ||
| 39 | OT | 0.00 | $0.00 | ||
| 40 | SUB TOT | 0.00 | $0.00 | ||
| 41 | Holiday | 0.00 | $0.00 | ||
| 42 | Total | 0.00 | $0.00 | ||
| 43 | P/Diem | $0.00 | |||
| 44 | |||||
| 45 | Weekly | Hours | Pay | ||
| 46 | Reg | 0.00 | $0.00 | ||
| 47 | OT | 0.00 | $0.00 | ||
| 48 | SUB TOT | 0.00 | $0.00 | ||
| 49 | Holiday | 0.00 | $0.00 | ||
| 50 | Total | 0.00 | $0.00 | ||
| 51 | P/Diem | $0.00 | |||
| 52 | |||||
| 53 | Weekly | Hours | Pay | ||
| 54 | Reg | 0.00 | $0.00 | ||
| 55 | OT | 0.00 | $0.00 | ||
| 56 | SUB TOT | 0.00 | $0.00 | ||
| 57 | Holiday | 0.00 | $0.00 | ||
| 58 | Total | 0.00 | $0.00 | ||
| 59 | P/Diem | $0.00 | |||
| 60 | |||||
| 61 | Weekly | Hours | Pay | ||
| 62 | Reg | 0.00 | $0.00 | ||
| 63 | OT | 0.00 | $0.00 | ||
| 64 | SUB TOT | 0.00 | $0.00 | ||
| 65 | Holiday | 0.00 | $0.00 | ||
| 66 | Total | 0.00 | $0.00 | ||
| 67 | P/Diem | $0.00 | |||
Time Entry | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| T6,T62,T54,T46,T38,T30,T22,T14 | T6 | =IF(T8>40,"40.00",T8) |
| T7,T63,T55,T47,T39,T31,T23,T15 | T7 | =IF(T8<41,"0.00",(T8-T6)) |
| T8,T64,T56,T48,T40,T32,T24,T16 | T8 | =SUM(H5:J11) |
| T9,T65,T57,T49,T41,T33,T25,T17 | T9 | =SUM(K5:K11) |
| U64,T66:U66,U56,T58:U58,U48,T50:U50,U40,T42:U42,U32,T34:U34,U24,T26:U26,U16,T18:U18,T10:U10,U8 | T10 | =SUM(T8:T9) |
| U6,U62,U54,U46,U38,U30,U22,U14 | U6 | =T6*P5 |
| U7,U63,U55,U47,U39,U31,U23,U15 | U7 | =T7*Q5 |
| U9,U65,U57,U49,U41,U33,U25,U17 | U9 | =T9*P5 |
| U11,U67,U59,U51,U43,U35,U27,U19 | U11 | =SUM(L5:L11) |
