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?

copying conditional formula
 

The first thing which comes to mind is that you are using ABSOLUTE reference
in your formulae (The $ signs in say $F$4). If you want to copy the formula
to other rows then remove the $ sign in front of the numeric portion of the
formula at appropriate places.

To test just remove at one location and see whether anything changes.

Let me know if this does not solve the problem. I will take a deeper look
then.



"Leachim12" wrote:

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?

copying conditional formula
 

Hi Sheeloo,

Thanks for getting back to me, I gave it a go. But, doesn't work Im afraid.
Keeping the row reference absolute works fine, the formula copies across and
refers to the correct number cell. However, the cells K & L don;t change
correctly, because the number portion of the reference is not absolute, the
logic function fails, as it starts moving along the cells to the right and I
end up testing against blank cells.

I need the August 08 '!$C$5 part to increase as so , August 08 '!$D$6,
August 08 '!$D$7 .....etc

It needs to do this in each of the 4 columns of the table

But,

I need the JoanneData!$K$5 part of the logic function to stay absolotue in
column 1 of my table, then change to JoanneData!$K$6 in column 2 of the
table and so on for each column.

Man this is pickling my grey matter. Appreciate you having a think too. I
could send on a copy of the sheet for you to look at if you like.

"Sheeloo" wrote:

The first thing which comes to mind is that you are using ABSOLUTE reference
in your formulae (The $ signs in say $F$4). If you want to copy the formula
to other rows then remove the $ sign in front of the numeric portion of the
formula at appropriate places.

To test just remove at one location and see whether anything changes.

Let me know if this does not solve the problem. I will take a deeper look
then.



"Leachim12" wrote:

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?

copying conditional formula
 

The thing you need to understand how absolute and relative references work.

Normally if you enter the formula =A1 in say D1 then if you copy it down
across rows then you get =A2 in D2, =A3 in D3 and so on.
If you copy it across columns then you get =B1 in E1, =C1 in F1 and so on.

As soon as you put $ either in front of the column letter or row number,
that portion turns into absolute reference and does not change.

This should fix your problem. Otherwise mail the file to me and I will try
to fix it.

id

"Leachim12" wrote:

Hi Sheeloo,

Thanks for getting back to me, I gave it a go. But, doesn't work Im afraid.
Keeping the row reference absolute works fine, the formula copies across and
refers to the correct number cell. However, the cells K & L don;t change
correctly, because the number portion of the reference is not absolute, the
logic function fails, as it starts moving along the cells to the right and I
end up testing against blank cells.

I need the August 08 '!$C$5 part to increase as so , August 08 '!$D$6,
August 08 '!$D$7 .....etc

It needs to do this in each of the 4 columns of the table

But,

I need the JoanneData!$K$5 part of the logic function to stay absolotue in
column 1 of my table, then change to JoanneData!$K$6 in column 2 of the
table and so on for each column.

Man this is pickling my grey matter. Appreciate you having a think too. I
could send on a copy of the sheet for you to look at if you like.

"Sheeloo" wrote:

The first thing which comes to mind is that you are using ABSOLUTE reference
in your formulae (The $ signs in say $F$4). If you want to copy the formula
to other rows then remove the $ sign in front of the numeric portion of the
formula at appropriate places.

To test just remove at one location and see whether anything changes.

Let me know if this does not solve the problem. I will take a deeper look
then.



"Leachim12" wrote:

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?

copying conditional formula
 

Thanks Sheeloo,

I get the absolute reference & relative references, just seems a bit tricky
on this one.

I did manage to get it to work, but took the long way round and used
Find-Replace to get the right references in each cell.

For interest I'd like to send you on a copy of the sheet I was working on,
and see if you can get the references correct, what's you remail address and
I'll forward it to you.



"Sheeloo" wrote:

The thing you need to understand how absolute and relative references work.

Normally if you enter the formula =A1 in say D1 then if you copy it down
across rows then you get =A2 in D2, =A3 in D3 and so on.
If you copy it across columns then you get =B1 in E1, =C1 in F1 and so on.

As soon as you put $ either in front of the column letter or row number,
that portion turns into absolute reference and does not change.

This should fix your problem. Otherwise mail the file to me and I will try
to fix it.

id

"Leachim12" wrote:

Hi Sheeloo,

Thanks for getting back to me, I gave it a go. But, doesn't work Im afraid.
Keeping the row reference absolute works fine, the formula copies across and
refers to the correct number cell. However, the cells K & L don;t change
correctly, because the number portion of the reference is not absolute, the
logic function fails, as it starts moving along the cells to the right and I
end up testing against blank cells.

I need the August 08 '!$C$5 part to increase as so , August 08 '!$D$6,
August 08 '!$D$7 .....etc

It needs to do this in each of the 4 columns of the table

But,

I need the JoanneData!$K$5 part of the logic function to stay absolotue in
column 1 of my table, then change to JoanneData!$K$6 in column 2 of the
table and so on for each column.

Man this is pickling my grey matter. Appreciate you having a think too. I
could send on a copy of the sheet for you to look at if you like.

"Sheeloo" wrote:

The first thing which comes to mind is that you are using ABSOLUTE reference
in your formulae (The $ signs in say $F$4). If you want to copy the formula
to other rows then remove the $ sign in front of the numeric portion of the
formula at appropriate places.

To test just remove at one location and see whether anything changes.

Let me know if this does not solve the problem. I will take a deeper look
then.



"Leachim12" wrote:

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?

copying conditional formula
 

pl. send it to . i will see and let you know.

glad you could find a workaround.

"Leachim12" wrote:

Thanks Sheeloo,

I get the absolute reference & relative references, just seems a bit tricky
on this one.

I did manage to get it to work, but took the long way round and used
Find-Replace to get the right references in each cell.

For interest I'd like to send you on a copy of the sheet I was working on,
and see if you can get the references correct, what's you remail address and
I'll forward it to you.



"Sheeloo" wrote:

The thing you need to understand how absolute and relative references work.

Normally if you enter the formula =A1 in say D1 then if you copy it down
across rows then you get =A2 in D2, =A3 in D3 and so on.
If you copy it across columns then you get =B1 in E1, =C1 in F1 and so on.

As soon as you put $ either in front of the column letter or row number,
that portion turns into absolute reference and does not change.

This should fix your problem. Otherwise mail the file to me and I will try
to fix it.

id

"Leachim12" wrote:

Hi Sheeloo,

Thanks for getting back to me, I gave it a go. But, doesn't work Im afraid.
Keeping the row reference absolute works fine, the formula copies across and
refers to the correct number cell. However, the cells K & L don;t change
correctly, because the number portion of the reference is not absolute, the
logic function fails, as it starts moving along the cells to the right and I
end up testing against blank cells.

I need the August 08 '!$C$5 part to increase as so , August 08 '!$D$6,
August 08 '!$D$7 .....etc

It needs to do this in each of the 4 columns of the table

But,

I need the JoanneData!$K$5 part of the logic function to stay absolotue in
column 1 of my table, then change to JoanneData!$K$6 in column 2 of the
table and so on for each column.

Man this is pickling my grey matter. Appreciate you having a think too. I
could send on a copy of the sheet for you to look at if you like.

"Sheeloo" wrote:

The first thing which comes to mind is that you are using ABSOLUTE reference
in your formulae (The $ signs in say $F$4). If you want to copy the formula
to other rows then remove the $ sign in front of the numeric portion of the
formula at appropriate places.

To test just remove at one location and see whether anything changes.

Let me know if this does not solve the problem. I will take a deeper look
then.



"Leachim12" wrote:

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?