Please Help--How can I get a Flag, if I try to schedule someone too many times

[Ray]

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.

 

[Guest]

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

 

[Guest]

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]

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










- 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

 

[Ray]

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.

- 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.

 

[Guest]

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,

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.


:
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.

 

[Ray]

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

On Aug 2, 11:12 pm, OssieMac <[email protected]>
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
:
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

 

[Ray]

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.

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

On Aug 2, 11:12 pm, OssieMac <[email protected]>
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
:
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