formula

springfield

New Member
Joined
Sep 14, 2002
Messages
3
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
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
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,147
Members
412,306
Latest member
fabio6
Top