MrExcel Publishing
Your One Stop for Excel Tips & Solutions

an IF formula, probably


Posted by kmurrell on January 01, 2002 9:48 AM

I'm wondering how I can ensure that the smae number in a log is never used twice by accident.

For example, I have created quite an info intensive log file with each entry having a unique reference number (ideally in numbering order from 101, 102, 103 ... on up). I just came across two entries in which the same reference number was used accidentally.

Now if there is a conditional IF formula I can use that would be the easiest I think. But as the log entries grow I need the IF formula to be able to look @ cells A'n' (last refernece number used) thru to cell A1 (very first reference number used, 101) & ensure that the new reference number being used hasn't already been previously used.

I know the answer is probably going to be very obvious when I see it, so thanx in advance.

cheers


Posted by Aladin Akyurek on January 01, 2002 10:39 AM

Assuming that A1 houses the first entry and A2 is empty.

Activate A2.
Activate Data|Validation.
Choose Custom for Allow in the Data Validation window.
Enter as formula:

=COUNTIF($A$1:A1,A2)=0

Copy A2 down as far as you need.

Aladin

==========

Posted by kmurrell on January 01, 2002 10:40 AM

countif formula

I just needed a data / validation entry... =countif($a$1:$a$1000,a1)=1

cheers


Posted by Aladin Akyurek on January 01, 2002 10:41 AM

Try my suggestion though...(Re: countif formula) [NT]