duplicate invoice formula

Cliff

So far, have not been able to work out a direct method of data validation that will take into account all the sheets. Here is a roundabout way of doing it :-

In each sheet put the following formula in row 2 of a column that is not used (let’s say column CF) and fill the whole column :-
=IF(COUNTIF(Week1,A2)+COUNTIF(Week2,A2)+ COUNTIF(Week3,A2) COUNTIF(Week4,A2) COUNTIF(Week5,A2)>1,"X","")

In each sheet, select column A and in the data validation formula box put :-
=CF<>”X”

If you don’t like the above, you could use data validation for each sheet instead, which I guess would be better than not having it at all and together with conditional formatting, you should be fairly well covered.
To set up data validation on individual sheets, select column A and in the data validation formula box put :-
=COUNTIF($A$2:$A$10000,A1)=1

Celia

Correction

I missed out some + signs :-

=IF(COUNTIF(Week1,A2)+COUNTIF(Week2,A2)+ COUNTIF(Week3,A2)+COUNTIF(Week4,A2)+COUNTIF(Week5,A2)>1,"X","")

Celia

thanks in advance.

Cliff
Two suggestions to do what you want are set out below.

In both cases I have assumed that :-
1.You have a workbook that already has four sheets named Week1, Week2, Week3, Week4.
2.Invoice numbers get entered in Column A of each sheet starting from cell A2 (cell A1 being used as the column header).
3.There will be no more than 10,000 invoices in any one week.

CONDITIONAL FORMATTING
This method will highlight any invoice numbers that are duplicates as and when an invoice number is entered in Column A of any of the four sheets.
1.Create four names as follows :-
Name : Week1
Refers to : =Week1!$A$2:$A$10000
Name : Week2
Refers to : =Week2!$A$2:$A$10000
Name : Week3
Refers to : =Week3!$A$2:$A$10000
Name : Week4
Refers to : =Week4!$A$2:$A$10000
2.Go to sheet “Week1” and select Column A
3.Go to Format/Conditional Formatting…
4.Select “Formula Is” in the first box and type into the second box :-
=IF(COUNTIF(Week1,A1)+ COUNTIF(Week2,A1)+ COUNTIF(Week3,A1)+ COUNTIF(Week4,A1)>1,TRUE,FALSE)
5.Select “Format…” and select the format you want to use to identify duplicate invoice numbers. Click OK.
6.Repeat steps 2 thru 5 for each of the sheets Week2, Week3, Week4

WORKSHEET FORMULA
This method identifies in a separate column (assumed to be Column B), all entries that are duplicates in Column A of all four sheets. The words “Duplicate No.” will be displayed in Column B against any duplicate invoice numbers.
1.Create names as in step 1 of CONDITIONAL FORMATTING above
2.Go to sheet “Week1” and enter the following formula in cell B2 :-
=IF(COUNTIF(Week1,A1)+COUNTIF(Week2,A1)+COUNTIF(Week3,A1) +COUNTIF(Week4,A1)>1,"Duplicate No.","")
3.Drag the formula in cell B2 down as far as required
2.Repeat steps 2 & 3 for each of the sheets Week2, Week3, Week4

Alternatively, it must also be possible to prevent users inputting duplicates by using data validation.

Celia

Re: Correction

Cliff
In the formulas wherever it reads A1, it should A2
Celia

Sorry not familiar with isna but
=ISNA(VLOOKUP(Sheet1!a3,Sheet2:Sheet3!A1:A2,1,FALSE))

jUST NAME YOUR SHEETS

I just read a formula that will notify the user when a duplicate invoice number is entered. The formula is this =isna(vlookup(A3,A$1:A2,1,false)) which I used and it worked great on the worksheet I entered it in. But I'm needing to use this for more than one worksheet in the same workbook. I know that the above formula will work on page 1, but how would I change it for 2nd, 3rd, etc.. sheets. I have a purchase journal set up in excel that has 1 page for each week in the month, labeled week 1, week 2, etc.. thanks in advance.

Celia,

Thank you so much for all your help. Everything has worked great and I appreciate your knowledge and help. For this though, I have it set up using your top formula above by turning duplicates red and a line through the invoice number. But with the people I work with I'd rather use data validation that will stop duplicates from being entered. I tried to enter your formula several ways, even combining the original formula yours and a couple others but I either get a formula error or a message saying I can't use data from other sheets. What am I doing wrong?? I modified your formula to fit my journal, I have 5 weeks and the cell range is c9:c38. Another question, know any excel chat rooms??