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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

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
 

Forum statistics

Threads
1,148,041
Messages
5,744,463
Members
423,877
Latest member
nlange41

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top