Prevent duplicates for 2 sheets + msgBox

[Nina97]

Hello everybody!

My question is, how am I able to prevent duplicates and give out a message box with informations (entry, sheetname, row)?

I do have 2 sheets named "Receiving" and "Shipping" and in both of them "Item Number" in column B (starting at row 3).

So, when I enter in one of them for example "12345" I want to have both sheets checked and if a duplicate is found, I would like to have a message box displayed with something like

"Invalid entry: 12345 already existing in Receiving row 249" and then have the entry cell cleared.


Thank you very much and kind regards. :)

[mikerickson]

You can do that with data validation, except for the "found on row" message, with the formula

=AND(ISERROR(MATCH(B3,Receiving!$B$3:$B$1000)),ISERROR(MATCH(B3,Shipping!$B $3:$B$1000)))