Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someone too many times
My workbook has eight worksheets; the first shows a list of all
employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someone to
Hi Ray,
If the employee numbers are on the odd numbered rows, what is on the even numbered rows.? Are they blank or do they contain other data? Also what version of xl are you using? Regards, OssieMac "Ray" wrote: My workbook has eight worksheets; the first shows a list of all employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someone to
Ray,
I have had a think about your request and will need even more info than I asked for earlier. You said that the worksheets are identical for each day of the week. Does that mean that the employees are on the same row in each worksheet? How does one identify if the employee has been scheduled? That is which column/s and what sort of data (times, numbers, text)? Are these cells blank if the employee is not scheduled? You also said that you know how to total just one sheet. Providing an example will probably help because then it is only a matter of obtaining a grand total for all sheets from the subtotals on each sheet. Regards, OssieMac "Ray" wrote: My workbook has eight worksheets; the first shows a list of all employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someone to
On Aug 2, 11:12 pm, OssieMac
wrote: Ray, I have had a think about your request and will need even more info than I asked for earlier. You said that the worksheets are identical for each day of the week. Does that mean that the employees are on the same row in each worksheet? How does one identify if the employee has been scheduled? That is which column/s and what sort of data (times, numbers, text)? Are these cells blank if the employee is not scheduled? You also said that you know how to total just one sheet. Providing an example will probably help because then it is only a matter of obtaining a grand total for all sheets from the subtotals on each sheet. Regards, OssieMac "Ray" wrote: My workbook has eight worksheets; the first shows a list of all employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much.- Hide quoted text - - Show quoted text - Hi Ossie, Thanks for your reply. I really appreciate it. Oops, when I said that the daily worksheets were identical, I mean that their formats are identical, but not the contents/values of their cells. Sorry about the ambiguity, there. Each day's sheet (Mon., Tue., etc.) looks like every other day's sheet, but will have different people scheduled to work on the various cash registers. For example, any given worker may be scheduled as a cashier on till #1, on Mon., for example, but might be scheduled to work on till #8, on Thursday. The workers are scheduled as teams of two people; one person is the cashier and the other is the assistant/"box boy." The cashiers' employee numbers are typed into the odd-numbered cells in column A (A5, A7..), and the assistants/"box boys" numbers are typed into the even-numbered cells of column A (A6, A8...). It's just the odd- numbered (cashiers) employee numbers that I need to look at. So, I need to, somehow, have Excel look down each & every day's column A, then flag me, if I have tried to use the same part-time person more than twice in a week as a cashier (i.e., flag me, if I have tried to type their employee number into an odd-numered cell of column A more than twice, total, in a week). When I said that I know how to total the number of times that I have used a person on one sheet, I meant that it would seem possible to just use =COUNTIF(A5:A35,[their employee number]). Range A5:A35 is the range in which their employee numbers are typed, when scheduling workers for each day. I need to count only the part-time cashiers, not the full-time cashiers. The part-time cashiers are designated with a "2," in column T of the first worksheet (the one with the employee roster), and the full-time cashiers are designated with a "1" on this worksheet. So, somehow, I need to have maybe some sort of "IF" - "AND" formula in place, it seems, such as: "IF the persons is a "2" (part-time cashier) AND their employee number has already been typed into a column A or the daily worksheets twice, THEN (a warning flag is given). I hope that all makes sense. Thanks, again, for your time and effort! --Ray |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someone to
On Aug 3, 11:10 am, Ray wrote:
On Aug 2, 11:12 pm, OssieMac wrote: Ray, I have had a think about your request and will need even more info than I asked for earlier. You said that the worksheets are identical for each day of the week. Does that mean that the employees are on the same row in each worksheet? How does one identify if the employee has been scheduled? That is which column/s and what sort of data (times, numbers, text)? Are these cells blank if the employee is not scheduled? You also said that you know how to total just one sheet. Providing an example will probably help because then it is only a matter of obtaining a grand total for all sheets from the subtotals on each sheet. Regards, OssieMac "Ray" wrote: My workbook has eight worksheets; the first shows a list of all employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much.- Hide quoted text - - Show quoted text - Hi Ossie, Thanks for your reply. I really appreciate it. Oops, when I said that the daily worksheets were identical, I mean that their formats are identical, but not the contents/values of their cells. Sorry about the ambiguity, there. Each day's sheet (Mon., Tue., etc.) looks like every other day's sheet, but will have different people scheduled to work on the various cash registers. For example, any given worker may be scheduled as a cashier on till #1, on Mon., for example, but might be scheduled to work on till #8, on Thursday. The workers are scheduled as teams of two people; one person is the cashier and the other is the assistant/"box boy." The cashiers' employee numbers are typed into the odd-numbered cells in column A (A5, A7..), and the assistants/"box boys" numbers are typed into the even-numbered cells of column A (A6, A8...). It's just the odd- numbered (cashiers) employee numbers that I need to look at. So, I need to, somehow, have Excel look down each & every day's column A, then flag me, if I have tried to use the same part-time person more than twice in a week as a cashier (i.e., flag me, if I have tried to type their employee number into an odd-numered cell of column A more than twice, total, in a week). When I said that I know how to total the number of times that I have used a person on one sheet, I meant that it would seem possible to just use =COUNTIF(A5:A35,[their employee number]). Range A5:A35 is the range in which their employee numbers are typed, when scheduling workers for each day. I need to count only the part-time cashiers, not the full-time cashiers. The part-time cashiers are designated with a "2," in column T of the first worksheet (the one with the employee roster), and the full-time cashiers are designated with a "1" on this worksheet. So, somehow, I need to have maybe some sort of "IF" - "AND" formula in place, it seems, such as: "IF the persons is a "2" (part-time cashier) AND their employee number has already been typed into a column A or the daily worksheets twice, THEN (a warning flag is given). I hope that all makes sense. Thanks, again, for your time and effort! --Ray- Hide quoted text - - Show quoted text - Hi Ossie, I forgot to mentioned that I am using Excel 2003. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someon
Hi Ray,
You were almost there with your reasoning. I have created a workbook with worksheets named Master, Mon, Tue, Wed, Thu, Fri, Sat, Sun to emulate yours. Master is your first sheet with the list of employee numbers and the Mon to Sun is your rosters. On the Master sheet allocate 8 columns and place column headers on them as follows:- Mon, Tue, Wed, Thu, Fri, Sat, Sun and Total. (Doesnt have to start with Mon; you just need the seven days of the week plus the Total. Note that the Mon Sun columns could be anywhere out to the right of the page and does not have to be in normal screen view. You only need the Total column in view. You can even hide the Mon to Sun columns. Assuming that the employee number is in column A with the first employee number in row 2 (A2) and the Perm/Casual indicator is in column T (T2), put the following formula in column Mon row 2 (same row as the first employee number). That will count how many times the employee appears on your Mon roster. Alter the range Mon!$A$5:$A$99 to include rows greater than the maximum number of rows that you are likely to have on a roster sheet. (It doesnt matter if you have too many rows which will allow for future expansion without having to adjust the formula in the future.) =IF(Master!$T2=2,COUNTIF(Mon!$A$5:$A$99,Master!$A2 ),) (Change the name Master to match your first sheet and Mon to match your Monday roster sheet name.) Note the absolute cell addressing ($ signs). This is essential on the roster range Mon!$A$5:$A$99 otherwise as you copy the formula down to the next line if it is not absolute then the roster range would change to Mon!A6:A100 and would not count the employee if it was in Mon!A5. Absolute addressing is also applied to column T and A but not the row numbers because you do not want these column to change as you copy the formulas across to the other columns but you do want the row numbers to change as they are copied down to other rows. Now copy the formula across the other columns Tue to Sun ON JUST THE FIRST ROW. You now need to edit each of the copied formulas. The formula in Tue column, change the roster sheet name from Mon to Tue roster sheet name, same for Wed, Thu etc... as per the following. =IF(Master!$T2=2,COUNTIF(Tue!$A$5:$A$99,Master!$A2 ),) =IF(Master!$T2=2,COUNTIF(Wed!$A$5:$A$99,Master!$A2 ),) Now in the last column (Total) simply sum the results across the page. =SUM(B2:H2) Select the 8 cells across the page and copy them down the page. No further editing required. You could then look at conditional formatting and color the cell background (fill) if the total is greater than 2. If you cant work out the conditional formatting from Help then let me know. Tip: Select the entire Total column when you do it. Regards, OssieMac "Ray" wrote: On Aug 3, 11:10 am, Ray wrote: On Aug 2, 11:12 pm, OssieMac wrote: Ray, I have had a think about your request and will need even more info than I asked for earlier. You said that the worksheets are identical for each day of the week. Does that mean that the employees are on the same row in each worksheet? How does one identify if the employee has been scheduled? That is which column/s and what sort of data (times, numbers, text)? Are these cells blank if the employee is not scheduled? You also said that you know how to total just one sheet. Providing an example will probably help because then it is only a matter of obtaining a grand total for all sheets from the subtotals on each sheet. Regards, OssieMac "Ray" wrote: My workbook has eight worksheets; the first shows a list of all employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much.- Hide quoted text - - Show quoted text - Hi Ossie, Thanks for your reply. I really appreciate it. Oops, when I said that the daily worksheets were identical, I mean that their formats are identical, but not the contents/values of their cells. Sorry about the ambiguity, there. Each day's sheet (Mon., Tue., etc.) looks like every other day's sheet, but will have different people scheduled to work on the various cash registers. For example, any given worker may be scheduled as a cashier on till #1, on Mon., for example, but might be scheduled to work on till #8, on Thursday. The workers are scheduled as teams of two people; one person is the cashier and the other is the assistant/"box boy." The cashiers' employee numbers are typed into the odd-numbered cells in column A (A5, A7..), and the assistants/"box boys" numbers are typed into the even-numbered cells of column A (A6, A8...). It's just the odd- numbered (cashiers) employee numbers that I need to look at. So, I need to, somehow, have Excel look down each & every day's column A, then flag me, if I have tried to use the same part-time person more than twice in a week as a cashier (i.e., flag me, if I have tried to type their employee number into an odd-numered cell of column A more than twice, total, in a week). When I said that I know how to total the number of times that I have used a person on one sheet, I meant that it would seem possible to just use =COUNTIF(A5:A35,[their employee number]). Range A5:A35 is the range in which their employee numbers are typed, when scheduling workers for each day. I need to count only the part-time cashiers, not the full-time cashiers. The part-time cashiers are designated with a "2," in column T of the first worksheet (the one with the employee roster), and the full-time cashiers are designated with a "1" on this worksheet. So, somehow, I need to have maybe some sort of "IF" - "AND" formula in place, it seems, such as: "IF the persons is a "2" (part-time cashier) AND their employee number has already been typed into a column A or the daily worksheets twice, THEN (a warning flag is given). I hope that all makes sense. Thanks, again, for your time and effort! --Ray- Hide quoted text - - Show quoted text - Hi Ossie, I forgot to mentioned that I am using Excel 2003. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someon
On Aug 3, 2:00 pm, OssieMac
wrote: Hi Ray, You were almost there with your reasoning. I have created a workbook with worksheets named Master, Mon, Tue, Wed, Thu, Fri, Sat, Sun to emulate yours. Master is your first sheet with the list of employee numbers and the Mon to Sun is your rosters. On the Master sheet allocate 8 columns and place column headers on them as follows:- Mon, Tue, Wed, Thu, Fri, Sat, Sun and Total. (Doesn't have to start with Mon; you just need the seven days of the week plus the Total. Note that the Mon - Sun columns could be anywhere out to the right of the page and does not have to be in normal screen view. You only need the Total column in view. You can even hide the Mon to Sun columns. Assuming that the employee number is in column A with the first employee number in row 2 (A2) and the Perm/Casual indicator is in column T (T2), put the following formula in column Mon row 2 (same row as the first employee number). That will count how many times the employee appears on your Mon roster. Alter the range Mon!$A$5:$A$99 to include rows greater than the maximum number of rows that you are likely to have on a roster sheet. (It doesn't matter if you have too many rows which will allow for future expansion without having to adjust the formula in the future.) =IF(Master!$T2=2,COUNTIF(Mon!$A$5:$A$99,Master!$A2 ),"") (Change the name Master to match your first sheet and Mon to match your Monday roster sheet name.) Note the absolute cell addressing ($ signs). This is essential on the roster range Mon!$A$5:$A$99 otherwise as you copy the formula down to the next line if it is not absolute then the roster range would change to Mon!A6:A100 and would not count the employee if it was in Mon!A5. Absolute addressing is also applied to column T and A but not the row numbers because you do not want these column to change as you copy the formulas across to the other columns but you do want the row numbers to change as they are copied down to other rows. Now copy the formula across the other columns Tue to Sun ON JUST THE FIRST ROW. You now need to edit each of the copied formulas. The formula in Tue column, change the roster sheet name from Mon to Tue roster sheet name, same for Wed, Thu etc... as per the following. =IF(Master!$T2=2,COUNTIF(Tue!$A$5:$A$99,Master!$A2 ),"") =IF(Master!$T2=2,COUNTIF(Wed!$A$5:$A$99,Master!$A2 ),"") Now in the last column (Total) simply sum the results across the page. =SUM(B2:H2) Select the 8 cells across the page and copy them down the page. No further editing required. You could then look at conditional formatting and color the cell background (fill) if the total is greater than 2. If you can't work out the conditional formatting from Help then let me know. Tip: Select the entire Total column when you do it. Regards, OssieMac "Ray" wrote: On Aug 3, 11:10 am, Ray wrote: On Aug 2, 11:12 pm, OssieMac wrote: Ray, I have had a think about your request and will need even more info than I asked for earlier. You said that the worksheets are identical for each day of the week. Does that mean that the employees are on the same row in each worksheet? How does one identify if the employee has been scheduled? That is which column/s and what sort of data (times, numbers, text)? Are these cells blank if the employee is not scheduled? You also said that you know how to total just one sheet. Providing an example will probably help because then it is only a matter of obtaining a grand total for all sheets from the subtotals on each sheet. Regards, OssieMac "Ray" wrote: My workbook has eight worksheets; the first shows a list of all employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much.- Hide quoted text - - Show quoted text - Hi Ossie, Thanks for your reply. I really appreciate it. Oops, when I said that the daily worksheets were identical, I mean that their formats are identical, but not the contents/values of their cells. Sorry about the ambiguity, there. Each day's sheet (Mon., Tue., etc.) looks like every other day's sheet, but will have different people scheduled to work on the various cash registers. For example, any given worker may be scheduled as a cashier on till #1, on Mon., for example, but might be scheduled to work on till #8, on Thursday. The workers are scheduled as teams of two people; one person is the cashier and the other is the assistant/"box boy." The cashiers' employee numbers are typed into the odd-numbered cells in column A (A5, A7..), and the assistants/"box boys" numbers are typed into the even-numbered cells of column A (A6, A8...). It's just the odd- numbered (cashiers) employee numbers that I need to look at. So, I need to, somehow, have Excel look down each & every day's column A, then flag me, if I have tried to use the same part-time person more than twice in a week as a cashier (i.e., flag me, if I have tried to type their employee number into an odd-numered cell of column A more than twice, total, in a week). When I said that I know how to total the number of times that I have used a person on one sheet, I meant that it would seem possible to just use =COUNTIF(A5:A35,[their employee number]). Range A5:A35 is the range in which their employee numbers are typed, when scheduling workers for each day. I need to count only the part-time cashiers, not the full-time cashiers. The part-time cashiers are designated with a "2," in column T of the first worksheet (the one with the employee roster), and the full-time cashiers are designated with a "1" on this worksheet. So, somehow, I need to have maybe some sort of "IF" - "AND" formula in place, it seems, such as: "IF the persons is a "2" (part-time cashier) AND their employee number has already been typed into a column A or the daily worksheets twice, THEN (a warning flag is given). I hope that all makes sense. Thanks, again, for your time and effort! --Ray- Hide quoted text - - Show quoted text - Hi Ossie, I forgot to mentioned that I am using Excel 2003.- Hide quoted text - - Show quoted text - Hi Ossie, Wow, you really went the extra mile!! Thank you for that great response. You have taken a lot of time and trouble, and I really do appreciate it. I'm really a newbie at all of this, so I'm not really sure that I got all of what you said, but I will give it a try and see what happens. You spelled it out clearly, but it's just that I'm a rookie at Excel. I am going to get on it right now, and will let you know how I did, later. Thanks so much!! --Ray |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please Help--How can I get a Flag, if I try to schedule someon
On Aug 3, 2:55 pm, Ray wrote:
On Aug 3, 2:00 pm, OssieMac wrote: Hi Ray, You were almost there with your reasoning. I have created a workbook with worksheets named Master, Mon, Tue, Wed, Thu, Fri, Sat, Sun to emulate yours. Master is your first sheet with the list of employee numbers and the Mon to Sun is your rosters. On the Master sheet allocate 8 columns and place column headers on them as follows:- Mon, Tue, Wed, Thu, Fri, Sat, Sun and Total. (Doesn't have to start with Mon; you just need the seven days of the week plus the Total. Note that the Mon - Sun columns could be anywhere out to the right of the page and does not have to be in normal screen view. You only need the Total column in view. You can even hide the Mon to Sun columns. Assuming that the employee number is in column A with the first employee number in row 2 (A2) and the Perm/Casual indicator is in column T (T2), put the following formula in column Mon row 2 (same row as the first employee number). That will count how many times the employee appears on your Mon roster. Alter the range Mon!$A$5:$A$99 to include rows greater than the maximum number of rows that you are likely to have on a roster sheet. (It doesn't matter if you have too many rows which will allow for future expansion without having to adjust the formula in the future.) =IF(Master!$T2=2,COUNTIF(Mon!$A$5:$A$99,Master!$A2 ),"") (Change the name Master to match your first sheet and Mon to match your Monday roster sheet name.) Note the absolute cell addressing ($ signs). This is essential on the roster range Mon!$A$5:$A$99 otherwise as you copy the formula down to the next line if it is not absolute then the roster range would change to Mon!A6:A100 and would not count the employee if it was in Mon!A5. Absolute addressing is also applied to column T and A but not the row numbers because you do not want these column to change as you copy the formulas across to the other columns but you do want the row numbers to change as they are copied down to other rows. Now copy the formula across the other columns Tue to Sun ON JUST THE FIRST ROW. You now need to edit each of the copied formulas. The formula in Tue column, change the roster sheet name from Mon to Tue roster sheet name, same for Wed, Thu etc... as per the following. =IF(Master!$T2=2,COUNTIF(Tue!$A$5:$A$99,Master!$A2 ),"") =IF(Master!$T2=2,COUNTIF(Wed!$A$5:$A$99,Master!$A2 ),"") Now in the last column (Total) simply sum the results across the page. =SUM(B2:H2) Select the 8 cells across the page and copy them down the page. No further editing required. You could then look at conditional formatting and color the cell background (fill) if the total is greater than 2. If you can't work out the conditional formatting from Help then let me know. Tip: Select the entire Total column when you do it. Regards, OssieMac "Ray" wrote: On Aug 3, 11:10 am, Ray wrote: On Aug 2, 11:12 pm, OssieMac wrote: Ray, I have had a think about your request and will need even more info than I asked for earlier. You said that the worksheets are identical for each day of the week. Does that mean that the employees are on the same row in each worksheet? How does one identify if the employee has been scheduled? That is which column/s and what sort of data (times, numbers, text)? Are these cells blank if the employee is not scheduled? You also said that you know how to total just one sheet. Providing an example will probably help because then it is only a matter of obtaining a grand total for all sheets from the subtotals on each sheet. Regards, OssieMac "Ray" wrote: My workbook has eight worksheets; the first shows a list of all employees, their employee numbers, and whether they are full-time or part-time workers (full-time workers are designated with "1," and part- time workers with a "2" in column "T" of this page). The other seven worksheets are identical to each other, each being for a day of the week (Sun. through Fri.). Each of the daily worksheets is simply used for scheduling people to be cashiers at one of twelve different cash registers. The cashiers' employee numbers are typed into column A of these sheets, on each of the odd-numbered rows, starting with row 5 (A5, A7...A27). Problem: I cannot schedule any of the part-time workers more than twice as a cashier, because if I do, then they will automatically be classified a full-time cashiers, which really messes up the payroll...and get me into big trouble. So, how can I do this? I need to get Excel to somehow do two "lookup"s: one to see if the employee being scheduled is a part-time worker (i.e., if they have a "2" in column "T" of the first worksheet), and then do a second "lookup," to count how many times their employee number has been typed into one of those odd-numbered cells, in column "A" of the daily worksheets....for the whole week. I can see how it would be done, if I were working with just the first worksheet (the list of names & numbers) and only one day to schedule, but I don't know how to get it to search through a whole week's worth of days, and flag me, if I have tried to schedule the same part-time worker more than twice, cumulatively, in the same week. Any help would be greatly appreciated so much.- Hide quoted text - - Show quoted text - Hi Ossie, Thanks for your reply. I really appreciate it. Oops, when I said that the daily worksheets were identical, I mean that their formats are identical, but not the contents/values of their cells. Sorry about the ambiguity, there. Each day's sheet (Mon., Tue., etc.) looks like every other day's sheet, but will have different people scheduled to work on the various cash registers. For example, any given worker may be scheduled as a cashier on till #1, on Mon., for example, but might be scheduled to work on till #8, on Thursday. The workers are scheduled as teams of two people; one person is the cashier and the other is the assistant/"box boy." The cashiers' employee numbers are typed into the odd-numbered cells in column A (A5, A7..), and the assistants/"box boys" numbers are typed into the even-numbered cells of column A (A6, A8...). It's just the odd- numbered (cashiers) employee numbers that I need to look at. So, I need to, somehow, have Excel look down each & every day's column A, then flag me, if I have tried to use the same part-time person more than twice in a week as a cashier (i.e., flag me, if I have tried to type their employee number into an odd-numered cell of column A more than twice, total, in a week). When I said that I know how to total the number of times that I have used a person on one sheet, I meant that it would seem possible to just use =COUNTIF(A5:A35,[their employee number]). Range A5:A35 is the range in which their employee numbers are typed, when scheduling workers for each day. I need to count only the part-time cashiers, not the full-time cashiers. The part-time cashiers are designated with a "2," in column T of the first worksheet (the one with the employee roster), and the full-time cashiers are designated with a "1" on this worksheet. So, somehow, I need to have maybe some sort of "IF" - "AND" formula in place, it seems, such as: "IF the persons is a "2" (part-time cashier) AND their employee number has already been typed into a column A or the daily worksheets twice, THEN (a warning flag is given). I hope that all makes sense. Thanks, again, for your time and effort! --Ray- Hide quoted text - - Show quoted text - Hi Ossie, I forgot to mentioned that I am using Excel 2003.- Hide quoted text - - Show quoted text - Hi Ossie, Wow, you really went the extra mile!! Thank you for that great response. You have taken a lot of time and trouble, and I really do appreciate it. I'm really a newbie at all of this, so I'm not really sure that I got all of what you said, but I will give it a try and see what happens. You spelled it out clearly, but it's just that I'm a rookie at Excel. I am going to get on it right now, and will let you know how I did, later. Thanks so much!! --Ray- Hide quoted text - - Show quoted text - Hi Ossie, Okay, that did it!! I can never thank you enough for your time and effort. It works just great. That is going to take a whole lot of stress off of doing the schedule at work. --Ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plot schedule-type data in graph, date and times as x & y axes | Charts and Charting in Excel | |||
How do I schedule multiple activities and times? | Excel Discussion (Misc queries) | |||
set up schedule of meeting space availability, rooms/ times | Excel Discussion (Misc queries) | |||
Create patient schedule based on master therapist schedule | Excel Discussion (Misc queries) | |||
Rows to be moved from one flag to another flag | Excel Programming |