Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating copying (Max Min) | Excel Worksheet Functions | |||
copying conditional sum (wizard) formula | Excel Discussion (Misc queries) | |||
copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
Copying a Conditional Sum | Excel Discussion (Misc queries) | |||
Copying conditional formatting...HELP PLEASE!!!! | Excel Worksheet Functions |