formula

[springfield]

I am trying to set up a spreadsheet with invoice numbers. When I enter a new number excel will look at the old numbers to see if there is a match, if so it will flag it and let me know. If not excel will do nothing and add the new number.

 

[Chris Davison]

assuming your invoice numbers are in column A, try conditional formatting where it colours any duplicates in red :

Highlight column A
format
conditional formatting
change "cell value is" to "formula is"
enter the formula :=COUNTIF($A$1:$A$1000,A1)>1
format
patterns
click on Ferrari red
okay
okay

this'll highlight in red any invoice number duplicates...... ie drawing your attention to them immediately : it's manual rather than automated, but it's what I tend you use for manual lists that require no duplicates

(obviously, adjust "A" to be your column and "1000" to be your expected column depth, bearing in mind future entries)



_________________
Hope this helps,
Chris

[Excel '97; Windows ME]
This message was edited by Chris Davison on 2002-09-15 08:07

 

[Brian from Maui]

In addition to Chris's idea, you might try using the same formula in Data Validation

 

[rikrak]

On 2002-09-15 09:57, Brian from Maui wrote:
In addition to Chris's idea, you might try using the same formula in Data Validation

Yes, use the same formula, but change it to =COUNTIF($A$1:$A$1000,A1)=1 :wink:
This message was edited by rikrak on 2002-09-15 14:43

 

[Brian from Maui]

rikrak

Auwe! Yes your right!