C
cox ng \(1\)
I need help in connecting cells and rows from one spreadsheet with multiple
tabs to another spreadsheet with multiple tabs. The first spreadsheet is
called "Time Sheet" (TS) and the second spreadsheet is called "Annual
Report" (AR).
Each tab on the TS has 25 employee names in a column with each employee
having a row containing 19 cells for information like hours, overtime, etc.
There are 3 rows for each employee representing 2 work weeks and 1 total.
There are 21 tabs in TS representing 21 two work week periods to make up one
year. Another words, each year on January 1st we will start with a new TS.
Each tab is identical other than the dates.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
1
1st Week Ending
3/18/2007
2
2nd Week Ending
3/25/2007
3
Pay Date
4/2/2007
4
5
Taken
Minus
Total
Total
Accord
Accord
6
Employee Name
Mon
Tue
Wed
Thur
Fri
Sat
Sun
Res
Vac
Hol
Res
Hrs
Reg
OT
Reg
On Call
PR/PM
Foreman
7
Anderson, Josh
8
3/18/2007
0.00
0.00
11.00
10.00
10.00
10.00
0.00
8.00
8.00
0.00
1.00
57.00
55.00
1.00
47.00
$52.00
$100.00
$25.00
9
10
3/25/2007
0.00
0.00
11.00
10.00
10.00
10.00
0.00
0.00
0.00
0.00
0.00
41.00
40.00
1.00
40.00
$50.00
$100.00
$25.00
11
Pay Period
0.00
0.00
22.00
20.00
20.00
20.00
0.00
8.00
8.00
0.00
1.00
98.00
95.00
2.00
87.00
$102.00
$200.00
$50.00
12
Blakley, Brett
13
3/18/2007
9.00
8.00
11.00
5.00
10.00
11.00
1.00
4.00
0.00
0.00
1.00
59.00
43.00
15.00
43.00
$50.00
$100.00
$25.00
14
15
3/25/2007
0.00
0.00
11.00
10.00
10.00
10.00
0.00
0.00
0.00
0.00
1.00
41.00
39.00
1.00
39.00
$50.00
$100.00
$25.00
16
Pay Period
9.00
8.00
22.00
15.00
20.00
21.00
1.00
4.00
0.00
0.00
2.00
100.00
82.00
16.00
82.00
$100.00
$200.00
$50.00
17
Eddings, Cindy
18
3/18/2007
0.00
0.00
11.00
6.00
10.00
10.00
0.00
0.00
0.00
0.00
0.00
37.00
37.00
0.00
37.00
$50.00
$100.00
$25.00
19
20
3/25/2007
0.00
0.00
15.00
10.00
10.00
10.00
0.00
0.00
8.00
0.00
0.00
53.00
48.00
5.00
40.00
$50.00
$100.00
$25.00
21
Pay Period
0.00
0.00
26.00
16.00
20.00
20.00
0.00
0.00
8.00
0.00
0.00
90.00
85.00
5.00
77.00
$100.00
$200.00
$50.00
Each tab on the AR has 52 dates in a column with each date having a row
containing 19 cells for information like hours, overtime, etc. There are 25
tabs in AR representing 25 employees.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
2007
AR
1
Anderson, Josh
4
Date
Str
OT
Fore
Job #
Done
Res
Tsec
Pro
Vac
Hol
Bonus
Wkend
Dis
Hlth
IRA
Fuel
Auto
Phone
5
7
12/24/2006
40
13.5
8
12/31/2006
40
7
8
72.40
9
1/7/2007
40
17
10
1/14/2007
40
17
72.40
11
1/21/2007
40
9
12
1/28/2007
40
13
72.40
13
2/4/2007
40
18
14
2/11/2007
40
47.5
72.40
109.12
15
2/18/2007
40
20
16
2/25/2007
40
9
72.40
17
3/4/2007
40
35
18
3/11/2007
22
0
72.40
19
3/18/2007
0.00
1.00
25.00
8
100.00
8
0
52.00
20
3/25/2007
0.00
1.00
21
4/1/2007
Each day we type into the TS cells the hours for each employee and any other
applicable information in the other cells. Each week we type into the AR
cells the applicable totals from the TS.
Is there a formula or conditional format that would automate the process? I
have color coded the appropriate cells for easier reference. If Josh
Anderson works 47 hours the week ending 3/18/2007, the number in cell P8 on
TS would automatically show in cell B19 on Josh's tab in the AR (cells in
orange). If Brett Blakley works 43 hours the same week, the number in cell
P13 on TS would automatically show in cell B19 on Brett's tab in the AR
(Brett's actual tabbed page is not pictured as an example). The next
following weeks we would repeat the process except there would be a new tab
in the AR every 2 weeks.
Thank you for any help you can offer,
Gary
tabs to another spreadsheet with multiple tabs. The first spreadsheet is
called "Time Sheet" (TS) and the second spreadsheet is called "Annual
Report" (AR).
Each tab on the TS has 25 employee names in a column with each employee
having a row containing 19 cells for information like hours, overtime, etc.
There are 3 rows for each employee representing 2 work weeks and 1 total.
There are 21 tabs in TS representing 21 two work week periods to make up one
year. Another words, each year on January 1st we will start with a new TS.
Each tab is identical other than the dates.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
1
1st Week Ending
3/18/2007
2
2nd Week Ending
3/25/2007
3
Pay Date
4/2/2007
4
5
Taken
Minus
Total
Total
Accord
Accord
6
Employee Name
Mon
Tue
Wed
Thur
Fri
Sat
Sun
Res
Vac
Hol
Res
Hrs
Reg
OT
Reg
On Call
PR/PM
Foreman
7
Anderson, Josh
8
3/18/2007
0.00
0.00
11.00
10.00
10.00
10.00
0.00
8.00
8.00
0.00
1.00
57.00
55.00
1.00
47.00
$52.00
$100.00
$25.00
9
10
3/25/2007
0.00
0.00
11.00
10.00
10.00
10.00
0.00
0.00
0.00
0.00
0.00
41.00
40.00
1.00
40.00
$50.00
$100.00
$25.00
11
Pay Period
0.00
0.00
22.00
20.00
20.00
20.00
0.00
8.00
8.00
0.00
1.00
98.00
95.00
2.00
87.00
$102.00
$200.00
$50.00
12
Blakley, Brett
13
3/18/2007
9.00
8.00
11.00
5.00
10.00
11.00
1.00
4.00
0.00
0.00
1.00
59.00
43.00
15.00
43.00
$50.00
$100.00
$25.00
14
15
3/25/2007
0.00
0.00
11.00
10.00
10.00
10.00
0.00
0.00
0.00
0.00
1.00
41.00
39.00
1.00
39.00
$50.00
$100.00
$25.00
16
Pay Period
9.00
8.00
22.00
15.00
20.00
21.00
1.00
4.00
0.00
0.00
2.00
100.00
82.00
16.00
82.00
$100.00
$200.00
$50.00
17
Eddings, Cindy
18
3/18/2007
0.00
0.00
11.00
6.00
10.00
10.00
0.00
0.00
0.00
0.00
0.00
37.00
37.00
0.00
37.00
$50.00
$100.00
$25.00
19
20
3/25/2007
0.00
0.00
15.00
10.00
10.00
10.00
0.00
0.00
8.00
0.00
0.00
53.00
48.00
5.00
40.00
$50.00
$100.00
$25.00
21
Pay Period
0.00
0.00
26.00
16.00
20.00
20.00
0.00
0.00
8.00
0.00
0.00
90.00
85.00
5.00
77.00
$100.00
$200.00
$50.00
Each tab on the AR has 52 dates in a column with each date having a row
containing 19 cells for information like hours, overtime, etc. There are 25
tabs in AR representing 25 employees.
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
2007
AR
1
Anderson, Josh
4
Date
Str
OT
Fore
Job #
Done
Res
Tsec
Pro
Vac
Hol
Bonus
Wkend
Dis
Hlth
IRA
Fuel
Auto
Phone
5
7
12/24/2006
40
13.5
8
12/31/2006
40
7
8
72.40
9
1/7/2007
40
17
10
1/14/2007
40
17
72.40
11
1/21/2007
40
9
12
1/28/2007
40
13
72.40
13
2/4/2007
40
18
14
2/11/2007
40
47.5
72.40
109.12
15
2/18/2007
40
20
16
2/25/2007
40
9
72.40
17
3/4/2007
40
35
18
3/11/2007
22
0
72.40
19
3/18/2007
0.00
1.00
25.00
8
100.00
8
0
52.00
20
3/25/2007
0.00
1.00
21
4/1/2007
Each day we type into the TS cells the hours for each employee and any other
applicable information in the other cells. Each week we type into the AR
cells the applicable totals from the TS.
Is there a formula or conditional format that would automate the process? I
have color coded the appropriate cells for easier reference. If Josh
Anderson works 47 hours the week ending 3/18/2007, the number in cell P8 on
TS would automatically show in cell B19 on Josh's tab in the AR (cells in
orange). If Brett Blakley works 43 hours the same week, the number in cell
P13 on TS would automatically show in cell B19 on Brett's tab in the AR
(Brett's actual tabbed page is not pictured as an example). The next
following weeks we would repeat the process except there would be a new tab
in the AR every 2 weeks.
Thank you for any help you can offer,
Gary