Help! Can't find bug in program

[Ray]

My program, which has eight worksheets, was working just fine...until
today. In column A of worksheet 1 (Roster), there is a list of
employee's numbers, and in column B of that worksheet, there is a
list
of employee's names. The program is one with which I schedule
workers, Mon. through Fri., by typing their employee numbers into the
left-hand column of each day of the week.

The other seven worksheets of my program are for each day of the week
(Mon, Tue, etc.). In column A of those worksheets, I type in an
employee number, and in column C I have a LOOKUP formula [for example
=VLOOKUP(A5,(Roster!A:B),2,FALSE] (brackets not included in that
formula) in place, which tells Excel to go to the first worksheet
(Roster), look up the employee number that was just typed in, then
return the name found. That part seems to be working fine.


However, the data validation seems to have gone crazy on me. I can't
schedule the same worker twice in the same day, of course, so I don't
want to be able to have the same employee's number or name twice in
column C (where the names are returned). To prevent me from making
that mistake, I have placed data validation in each cell of that
column, using this in the "Formula" box: =COUNTIF($C$5:$C$27,C5)=1
In the "Style" box, I chose "Stop," and in the "Message" box, I typed
"You have already scheduled him/her!" It all seems to be working
fine,
until I get down toward the bottom of the column where I am typing in
their numbers. It will stop me from typing in an employee's number,
giving me the warning "You have already scheduled him/her!," even
though I really haven't. I have gone back up and down the column,
carefully looking to see if I have already used that person's number,
but I really haven't. Then I went back to the Roster worksheet, to
see
if there are, mistakenly, two people with the same employee number,
and there aren't. I checked the formulas, including their ranges, and
everything seems to be correct.


Does anybody have an idea of what might be going on, here?

 

[Ronald Dodge]

Well for one, I see that "C5" is within the range of "$C$5:$C$27", so you
will need to adjust this. Maybe you meant to do the following in C28:

=COUNTIF($C$5:$C27,$C28)=1


--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Guest]

Ronald,
I would guess you misread the question. If he is entering data in C5 and
using that formula to restrict duplicates using data validation, why would he
want to count the number of occurancesin C5:C27 that hold the value in C28?
or any cell but the value being entered in the cell that has the data
validation?

http://www.cpearson.com/excel/NoDupEntry.aspx

--------------------------------
Anyway, to the Original Poster. Your formula looks good. Have you actually
checked the data validation setting in the cells that are causing you
problems. Perhaps you entered the data validation formulas in them
separately and they are incorrect. I would look at each one individually.

--
Regards,
Tom Ogilvy

Well for one, I see that "C5" is within the range of "$C$5:$C$27", so you
will need to adjust this. Maybe you meant to do the following in C28:

=COUNTIF($C$5:$C27,$C28)=1


--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

My program, which has eight worksheets, was working just fine...until
today. In column A of worksheet 1 (Roster), there is a list of
employee's numbers, and in column B of that worksheet, there is a
list
of employee's names. The program is one with which I schedule
workers, Mon. through Fri., by typing their employee numbers into the
left-hand column of each day of the week.

The other seven worksheets of my program are for each day of the week
(Mon, Tue, etc.). In column A of those worksheets, I type in an
employee number, and in column C I have a LOOKUP formula [for example
=VLOOKUP(A5,(Roster!A:B),2,FALSE] (brackets not included in that
formula) in place, which tells Excel to go to the first worksheet
(Roster), look up the employee number that was just typed in, then
return the name found. That part seems to be working fine.


However, the data validation seems to have gone crazy on me. I can't
schedule the same worker twice in the same day, of course, so I don't
want to be able to have the same employee's number or name twice in
column C (where the names are returned). To prevent me from making
that mistake, I have placed data validation in each cell of that
column, using this in the "Formula" box: =COUNTIF($C$5:$C$27,C5)=1
In the "Style" box, I chose "Stop," and in the "Message" box, I typed
"You have already scheduled him/her!" It all seems to be working
fine,
until I get down toward the bottom of the column where I am typing in
their numbers. It will stop me from typing in an employee's number,
giving me the warning "You have already scheduled him/her!," even
though I really haven't. I have gone back up and down the column,
carefully looking to see if I have already used that person's number,
but I really haven't. Then I went back to the Roster worksheet, to
see
if there are, mistakenly, two people with the same employee number,
and there aren't. I checked the formulas, including their ranges, and
everything seems to be correct.


Does anybody have an idea of what might be going on, here?

 

[Ronald Dodge]

My mistake, misread the purpose of the formula itself as I was thinking
above, but it's looking at the whole range, so I was thinking maybe it was
on C28 rather than on C5. I must also agree the formula looks okay,
provided this formula is on cell "C5".

I would find it kinda hard to believe it's some other issue unless you have
some user event or something else taking place, so just as Tom mentioned,
double check your validation. The other thing you may want to do is copy
Cell "C5" then paste special the validation onto cells C6:C27.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,
I would guess you misread the question. If he is entering data in C5 and
using that formula to restrict duplicates using data validation, why would
he
want to count the number of occurancesin C5:C27 that hold the value in
C28?
or any cell but the value being entered in the cell that has the data
validation?

http://www.cpearson.com/excel/NoDupEntry.aspx

--------------------------------
Anyway, to the Original Poster. Your formula looks good. Have you
actually
checked the data validation setting in the cells that are causing you
problems. Perhaps you entered the data validation formulas in them
separately and they are incorrect. I would look at each one individually.

--
Regards,
Tom Ogilvy

Well for one, I see that "C5" is within the range of "$C$5:$C$27", so you
will need to adjust this. Maybe you meant to do the following in C28:

=COUNTIF($C$5:$C27,$C28)=1


--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

My program, which has eight worksheets, was working just fine...until
today. In column A of worksheet 1 (Roster), there is a list of
employee's numbers, and in column B of that worksheet, there is a
list
of employee's names. The program is one with which I schedule
workers, Mon. through Fri., by typing their employee numbers into the
left-hand column of each day of the week.

The other seven worksheets of my program are for each day of the week
(Mon, Tue, etc.). In column A of those worksheets, I type in an
employee number, and in column C I have a LOOKUP formula [for example
=VLOOKUP(A5,(Roster!A:B),2,FALSE] (brackets not included in that
formula) in place, which tells Excel to go to the first worksheet
(Roster), look up the employee number that was just typed in, then
return the name found. That part seems to be working fine.


However, the data validation seems to have gone crazy on me. I can't
schedule the same worker twice in the same day, of course, so I don't
want to be able to have the same employee's number or name twice in
column C (where the names are returned). To prevent me from making
that mistake, I have placed data validation in each cell of that
column, using this in the "Formula" box: =COUNTIF($C$5:$C$27,C5)=1
In the "Style" box, I chose "Stop," and in the "Message" box, I typed
"You have already scheduled him/her!" It all seems to be working
fine,
until I get down toward the bottom of the column where I am typing in
their numbers. It will stop me from typing in an employee's number,
giving me the warning "You have already scheduled him/her!," even
though I really haven't. I have gone back up and down the column,
carefully looking to see if I have already used that person's number,
but I really haven't. Then I went back to the Roster worksheet, to
see
if there are, mistakenly, two people with the same employee number,
and there aren't. I checked the formulas, including their ranges, and
everything seems to be correct.


Does anybody have an idea of what might be going on, here?

 

[Ray]

My mistake, misread the purpose of the formula itself as I was thinking
above, but it's looking at the whole range, so I was thinking maybe it was
on C28 rather than on C5. I must also agree the formula looks okay,
provided this formula is on cell "C5".

I would find it kinda hard to believe it's some other issue unless you have
some user event or something else taking place, so just as Tom mentioned,
double check your validation. The other thing you may want to do is copy
Cell "C5" then paste special the validation onto cells C6:C27.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

Ronald,
I would guess you misread the question. If he is entering data in C5 and
using that formula to restrict duplicates using data validation, why would
he
want to count the number of occurancesin C5:C27 that hold the value in
C28?
or any cell but the value being entered in the cell that has the data
validation?

--------------------------------
Anyway, to the Original Poster. Your formula looks good. Have you
actually
checked the data validation setting in the cells that are causing you
problems. Perhaps you entered the data validation formulas in them
separately and they are incorrect. I would look at each one individually.

Well for one, I see that "C5" is within the range of "$C$5:$C$27", so you
will need to adjust this. Maybe you meant to do the following in C28:
=COUNTIF($C$5:$C27,$C28)=1
--
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
My program, which has eight worksheets, was working just fine...until
today. In column A of worksheet 1 (Roster), there is a list of
employee's numbers, and in column B of that worksheet, there is a
list
of employee's names. The program is one with which I schedule
workers, Mon. through Fri., by typing their employee numbers into the
left-hand column of each day of the week.
The other seven worksheets of my program are for each day of the week
(Mon, Tue, etc.). In column A of those worksheets, I type in an
employee number, and in column C I have a LOOKUP formula [for example
=VLOOKUP(A5,(Roster!A:B),2,FALSE] (brackets not included in that
formula) in place, which tells Excel to go to the first worksheet
(Roster), look up the employee number that was just typed in, then
return the name found. That part seems to be working fine.
However, the data validation seems to have gone crazy on me. I can't
schedule the same worker twice in the same day, of course, so I don't
want to be able to have the same employee's number or name twice in
column C (where the names are returned). To prevent me from making
that mistake, I have placed data validation in each cell of that
column, using this in the "Formula" box: =COUNTIF($C$5:$C$27,C5)=1
In the "Style" box, I chose "Stop," and in the "Message" box, I typed
"You have already scheduled him/her!" It all seems to be working
fine,
until I get down toward the bottom of the column where I am typing in
their numbers. It will stop me from typing in an employee's number,
giving me the warning "You have already scheduled him/her!," even
though I really haven't. I have gone back up and down the column,
carefully looking to see if I have already used that person's number,
but I really haven't. Then I went back to the Roster worksheet, to
see
if there are, mistakenly, two people with the same employee number,
and there aren't. I checked the formulas, including their ranges, and
everything seems to be correct.
Does anybody have an idea of what might be going on, here?- Hide quoted text -

- Show quoted text -

Thanks, guys, I'll give it another close look...at each individual
cell. I appreciate your time and effort.


--Ray