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


"peaspud" wrote:

> 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
>
>
>
> "Ron Coderre" wrote:
>
> > 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
> >
> >
> > "peaspud" wrote:
> >
> > > 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.
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > 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.
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > "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)
> > > > > > > > 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" 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