copying conditional formula
I have a workbook which captures the time an employee logs into their system.
There are 4 different shifts:
08:30
09:00
09:30
10:00
Each employee has their own row, the columns along this row denote the day
of the month. The employee has a different shift each week. I have entered
the condtional formula below on a seperate worksheet:
IF(AND('August '08 '!$F$4FinData!$K$5, 'August '08 '!$F$4<FinData!$L$6),
'August 08 '!$F$4, "-")
This formula tests each cell on the employee row against a table of values (
that's what K5 & L6 are referring too):
08:00 - 08:50 k5, l5
08:51 - 09:20 k6, l6
09:21 - 09:50 k7, l7
09:51 - 10:20 k8, l8
I can then take an average login time for each shift. It works great for an
individual row. But, and here's where Im stuck.....I want to apply this to
another 9 people, so another 9 rows. But when I create a new worksheet for
the next employee, the conditional formula still refers to the row of the
previous employee. Which means I have to go through the table and manually
change all of the cell references!!
Is there a quicker and easier way of getting the formula to refer to the
correct row and cells?
|