Excel 2007 : compare IP addresses on multiple sheets for duplicates

Hello all,

I have been trying to figure out how to compare ip addresses on multiple different sheets for duplicates. I don't know the best way to do this so it would be great if I could get some help. I have attached the current excel spreadsheet for you. I would like to be able to have the system highlight any current duplicates I might have and also in the future when adding an IP address it will give some kind of indication that the number already exsists on another sheet.

Any help would be greatly appericated

Patrick

Hm well I'm not sure, but what you could do is either create an extra column on each worksheet to check across all worksheets if the value entered already exists or enter the new value in sheet1 before adding it onto one of the masterlists.

For the former, assume using column I as the checker:
Put in I3 of any sheet and copy down:
=COUNTIFS(B1-BME!B:B,B3,B3!B:B,B3,B8!B:B,B3,B16!B:B,B3,'Fisher House'!B:B,B3,'VA Police'!B:B,B3)

Then use conditional formatting on the column I with Icon Sets and select Red for if Value is greater than 0, and Green for smaller or equal to 0.

For the latter method, you could enter the IP to be checked in A1 and the checker code in B1 and amend all references to B3 to A1 for the code above (other than the sheet B3 itself)