Formula Problems (Excel 2000)

Topic

New Reply

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!

Reply | Quote

Replies

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)

Reply | Quote

See if this does what you want:

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

Reply | Quote

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”

Aladin

Reply | Quote

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

Reply | Quote

I believe you confounded me with the original poster (OP).

It’s of course a good idea if that’s what the OP wants.

Aladin

Reply | Quote

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!

Reply | Quote