Results 1 to 7 of 7
Thread: Formula Problems (Excel 2000)

20020422, 16:11 #1
 Join Date
 Mar 2001
 Posts
 26
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula Problems (Excel 2000)
I'm having trouble with a silly formula. I want to be able to enter names on a list  and know if I've repeated them previously. Right now I can't get the formula to look at a range  it keeps taking the top value. I've written it two ways and neither quite work. Any help would be appreciated!
=EXACT(A8,$A$2:A7)
=IF(A14=$A$2:A13,"No","Yes")
I've thought about LOOKUP but I'm not sure how to use this. Seems like it should be simple!

20020422, 16:43 #2
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Problems (Excel 2000)
It seems that you want to check whether a given entry occurs more than once in exactly the same shape. If so,
in A2 enter and copy down:
=SUMPRODUCT((EXACT($A$2:$A$10,A2))+0)Microsoft MVP  Excel

20020422, 17:42 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Problems (Excel 2000)
See if this does what you want:
<pre>=IF(ISERROR(VLOOKUP(A14,A1:A13,1,FALSE)),"No" ,"Yes")
</pre>
Legare Coleman

20020422, 20:23 #4
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Problems (Excel 2000)
If that's what is wanted, a COUNTIF formula would suffice:
=IF(COUNTIF(A1:A13,A14),"Yes","No")
or just
=COUNTIF(A1:A13,A4)
where the cell of the formula can be custom formatted as:
[=0]"No";"Yes"
AladinMicrosoft MVP  Excel

20020423, 11:35 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Re: Formula Problems (Excel 2000)
To prevent duplicate entries in a list I use Exce's Data>Validation option.
Suppose your list range is E13:E62
Put cellpointer In cell [e13],
Select Data from the top panel command bar and then select Validation from the menu dropdown.
On the sheet tab labeeled 'Setting:
set 'Allow:' dropdown to 'Custom'
check the box labelled 'Ignore blank'
In the Formula: section type
=COUNTIF($E$13:$E$62,E13)<2
(Note dollar signs are important!)
Click the tab heading 'Error Alert'
Check the box labelled 'Show erro alert after invalid data is entered'
For the 'Style' dropdown, pick 'Stop'
For the 'Title', enter something like:
Duplicate entry found!
For the 'Error message:' entry, type something like
This entry has already been entered in the list!
Only one entry allowed.
Now copy the cell and use
Edit  PasteSpecial  Validation
to copy to the list entry range.
That's it!
Now, whenever you try to enter something that has already been entered anywhere in the specified list range, you will see the error message and the new entry will be disallowed.
zeddy

20020423, 13:39 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Problems (Excel 2000)
I believe you confounded me with the original poster (OP).
It's of course a good idea if that's what the OP wants.
AladinMicrosoft MVP  Excel

20020423, 21:12 #7
 Join Date
 Mar 2001
 Posts
 26
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula Problems (Excel 2000)
Thank you all for your suggestions and your help! The formula that Legare Coleman gave me is the one that gave me the desired result. I wanted to know if something had been typed previously  was what I typed a new client or an existing one. That formula gave me that information  thank you, thank you! Now I just have to figure out why it does that! :)