HI,
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
I know it sounds weird but im doing a dream team at work and i need as much
help as i can get!!
Thanks

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
DataValidation
Allow: Custom
Formula: =COUNTIF($A$1:$A$10,A1)<=3
Click the [OK] button

That will allow the same entry in that range a maximum of 3 times.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

HI,
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
I know it sounds weird but im doing a dream team at work and i need as much
help as i can get!!
Thanks

Thank for your advice ron,but now that i have put in the suggest formula, it
wont let me put anything in those cells. "A user has restricted values that
can be enterd in this cell" error message appears?? im sure i have entered it
correctly??
Thanks

"Ron Coderre" wrote:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
DataValidation
Allow: Custom
Formula: =COUNTIF($A$1:$A$10,A1)<=3
Click the [OK] button

That will allow the same entry in that range a maximum of 3 times.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

HI,
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
I know it sounds weird but im doing a dream team at work and i need as much
help as i can get!!
Thanks

You should only get that message if the same value is already in the
referenced range 3 or more times. If that is not the case, then the
validation formula needs to be adjusted.

However, if you only want the user to be warned, but still allowed to enter
the value then:

Select the range to be validated.
DataValidation
(adjust your validation constraints, if necessary)
Select the "Error Alert" tab
Set the Style to either Warning or Information.
Click the [OK] button.

Now, if users enter a value for the 4th time...they will only be notified of
the situation, but still allowed to enter the same value.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

Thank for your advice ron,but now that i have put in the suggest formula, it
wont let me put anything in those cells. "A user has restricted values that
can be enterd in this cell" error message appears?? im sure i have entered it
correctly??
Thanks

"Ron Coderre" wrote:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
DataValidation
Allow: Custom
Formula: =COUNTIF($A$1:$A$10,A1)<=3
Click the [OK] button

That will allow the same entry in that range a maximum of 3 times.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

HI,
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
I know it sounds weird but im doing a dream team at work and i need as much
help as i can get!!
Thanks

Thanks ron,
that has helped.i now understand how it works. One more question (sorry for
being so cheeky) if i wanted to do the same with words rather than
numbers,how would i correct the foumula.
e.g. If the word "Dad" was used more than twice.
Thanks again for your help.
Greg.Nixon

"Ron Coderre" wrote:

You should only get that message if the same value is already in the
referenced range 3 or more times. If that is not the case, then the
validation formula needs to be adjusted.

However, if you only want the user to be warned, but still allowed to enter
the value then:

Select the range to be validated.
DataValidation
(adjust your validation constraints, if necessary)
Select the "Error Alert" tab
Set the Style to either Warning or Information.
Click the [OK] button.

Now, if users enter a value for the 4th time...they will only be notified of
the situation, but still allowed to enter the same value.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

Thank for your advice ron,but now that i have put in the suggest formula, it
wont let me put anything in those cells. "A user has restricted values that
can be enterd in this cell" error message appears?? im sure i have entered it
correctly??
Thanks

"Ron Coderre" wrote:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
DataValidation
Allow: Custom
Formula: =COUNTIF($A$1:$A$10,A1)<=3
Click the [OK] button

That will allow the same entry in that range a maximum of 3 times.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

HI,
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
I know it sounds weird but im doing a dream team at work and i need as much
help as i can get!!
Thanks

If you mean that you only want to ensure that the word "Dad" is not entered
more than twice, then try this formula in the Data Validation:

Formula: =COUNTIF($A$1:$A$10,"Dad")<=2

I hope that helps?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

Thanks ron,
that has helped.i now understand how it works. One more question (sorry for
being so cheeky) if i wanted to do the same with words rather than
numbers,how would i correct the foumula.
e.g. If the word "Dad" was used more than twice.
Thanks again for your help.
Greg.Nixon

"Ron Coderre" wrote:

You should only get that message if the same value is already in the
referenced range 3 or more times. If that is not the case, then the
validation formula needs to be adjusted.

However, if you only want the user to be warned, but still allowed to enter
the value then:

Select the range to be validated.
DataValidation
(adjust your validation constraints, if necessary)
Select the "Error Alert" tab
Set the Style to either Warning or Information.
Click the [OK] button.

Now, if users enter a value for the 4th time...they will only be notified of
the situation, but still allowed to enter the same value.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

Thank for your advice ron,but now that i have put in the suggest formula, it
wont let me put anything in those cells. "A user has restricted values that
can be enterd in this cell" error message appears?? im sure i have entered it
correctly??
Thanks

"Ron Coderre" wrote:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
DataValidation
Allow: Custom
Formula: =COUNTIF($A$1:$A$10,A1)<=3
Click the [OK] button

That will allow the same entry in that range a maximum of 3 times.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"peaspud" wrote:

HI,
Is there a function in excel that alerts you if you have entered a number
(or word!) more than an agreed amount of times??
e.g. If you have agreed not to input the number 7 more than 3 times in a
selected range, but then do so, will excel inform you??
I know it sounds weird but im doing a dream team at work and i need as much
help as i can get!!
Thanks