Alert in Excel???

[peaspud]

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

 

[Ron Coderre]

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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]

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

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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

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

 

[Ron Coderre]

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.
Data>Validation
(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

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

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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

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

 

[peaspud]

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

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.
Data>Validation
(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

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

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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


:

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

 

[Ron Coderre]

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

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

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.
Data>Validation
(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

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

:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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


:

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

 

[peaspud]

Ron your are a marvel,
Thank you very much for your help.Can i ask just one more favour and then
i'll leave you alone?? Can i input more than one name or number?
e.g "Dad Mum Son etc,etc". Is that possible?
Once again thanks for your help.
Greg.

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

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

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.
Data>Validation
(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


:

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

:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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


:

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

 

[Ron Coderre]

I'm not exactly sure what you're asking, but I'll take a guess.

If you want to limit user input to items on your list, but no item may
appear more than 2 times, try this:

Create your list an empty section of your worksheet, or better yet, on
another sheet...then name that range.

Example:
On another sheet
A1: MyList
A2: Dad
A3: Mum
A4: Son

Select A2:A4
Insert>Names>Define
Names in workbook: MyList
Refers to: (already selected: A2:A4)
Click the [OK] button

On the input sheet, select the input range A1:A10, with A1 as the active cell.
Data>Validation
Allow: Custom
Formula: =AND(ISNUMBER(MATCH(A1,MyList,0)),COUNTIF($A$1:$A$10,A1)<=2)
Click the [OK] button

Does that give you something to work with?

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

XL2002, WinXP-Pro

Ron your are a marvel,
Thank you very much for your help.Can i ask just one more favour and then
i'll leave you alone?? Can i input more than one name or number?
e.g "Dad Mum Son etc,etc". Is that possible?
Once again thanks for your help.
Greg.

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

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

:

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.
Data>Validation
(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


:

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

:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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


:

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

 

[peaspud]

Hi Ron,
Tried your suggestion, but ii could not get it to work. I will give you a
brief explanation of what im trying to achive, and then maybe you could help
me. Im running a Dream Team (football/soccer) at work,and im trying to limit
any possible mistakes that may be made when people are swapping players at
transfer time.To cut a long story short,one of the many rules is that "You
must pick no more than two players from any one Premiership side." So i was
hoping that there was some formula that would help me detect if i was
inputting the same team more than twice. Its just that its hard to keep track
of 80 teams or more when you are doing it alone. If it would help you to
understand it a bit more, i could send you an excel sheet with team on it?
Thanks for your help and patience
Greg

I'm not exactly sure what you're asking, but I'll take a guess.

If you want to limit user input to items on your list, but no item may
appear more than 2 times, try this:

Create your list an empty section of your worksheet, or better yet, on
another sheet...then name that range.

Example:
On another sheet
A1: MyList
A2: Dad
A3: Mum
A4: Son

Select A2:A4
Insert>Names>Define
Names in workbook: MyList
Refers to: (already selected: A2:A4)
Click the [OK] button

On the input sheet, select the input range A1:A10, with A1 as the active cell.
Data>Validation
Allow: Custom
Formula: =AND(ISNUMBER(MATCH(A1,MyList,0)),COUNTIF($A$1:$A$10,A1)<=2)
Click the [OK] button

Does that give you something to work with?

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

XL2002, WinXP-Pro

Ron your are a marvel,
Thank you very much for your help.Can i ask just one more favour and then
i'll leave you alone?? Can i input more than one name or number?
e.g "Dad Mum Son etc,etc". Is that possible?
Once again thanks for your help.
Greg.

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


:

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

:

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.
Data>Validation
(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


:

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

:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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


:

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

 

[Ron Coderre]

Before reinventing the wheel, I'm hoping there's a working dream team model
out there somewhere. Perhaps one of the forum members who shares your
enthusiasm for Dream Team sports leagues already has a solution he or she can
share with you.

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

XL2002, WinXP-Pro

Hi Ron,
Tried your suggestion, but ii could not get it to work. I will give you a
brief explanation of what im trying to achive, and then maybe you could help
me. Im running a Dream Team (football/soccer) at work,and im trying to limit
any possible mistakes that may be made when people are swapping players at
transfer time.To cut a long story short,one of the many rules is that "You
must pick no more than two players from any one Premiership side." So i was
hoping that there was some formula that would help me detect if i was
inputting the same team more than twice. Its just that its hard to keep track
of 80 teams or more when you are doing it alone. If it would help you to
understand it a bit more, i could send you an excel sheet with team on it?
Thanks for your help and patience
Greg

I'm not exactly sure what you're asking, but I'll take a guess.

If you want to limit user input to items on your list, but no item may
appear more than 2 times, try this:

Create your list an empty section of your worksheet, or better yet, on
another sheet...then name that range.

Example:
On another sheet
A1: MyList
A2: Dad
A3: Mum
A4: Son

Select A2:A4
Insert>Names>Define
Names in workbook: MyList
Refers to: (already selected: A2:A4)
Click the [OK] button

On the input sheet, select the input range A1:A10, with A1 as the active cell.
Data>Validation
Allow: Custom
Formula: =AND(ISNUMBER(MATCH(A1,MyList,0)),COUNTIF($A$1:$A$10,A1)<=2)
Click the [OK] button

Does that give you something to work with?

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

XL2002, WinXP-Pro

Ron your are a marvel,
Thank you very much for your help.Can i ask just one more favour and then
i'll leave you alone?? Can i input more than one name or number?
e.g "Dad Mum Son etc,etc". Is that possible?
Once again thanks for your help.
Greg.

:

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


:

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

:

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.
Data>Validation
(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


:

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

:

Try this:

For cells A1:A10

Select A1:A10 (with A1 as the active cell)
Data>Validation
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


:

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