# Thread: Formula Problems (Excel 2000)

1. ## 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!

2. ## 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)

3. ## Re: Formula Problems (Excel 2000)

See if this does what you want:

<pre>=IF(ISERROR(VLOOKUP(A14,A1:A13,1,FALSE)),"No" ,"Yes")
</pre>

4. ## 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"

5. ## 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!)
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

6. ## 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.