Data Validation across multiple tabs or sheets

Hi,
I have used conditional formatting quite often now, and with much success. However I now have a requirement to use conditional formatting across more than 1 sheet (or tabs). Basically I have a drop down list of usable IP addresses. This drop down list is presented on more than one sheet. I have to be very careful however, not to attribute or duplicate an IP address to more than one device. I have therefore used the "Duplicate Values" rule in conditional formatting to highlight in red any cell where I select a duplicate IP address.

This works just fine and it is instantly apparent, with both offending IP addresses lighting up in red, should I accidentally select a duplicate IP address on Sheet1. However if I have an IP address on Sheet2, and then select the same address on Sheet1, I can find no way to get a single conditional formatting rule that covers both sheets! In the rule, I have tried to use the following for the (applies to) cell of the rule:

=Cameras!$C$3:$C$30,Monitors!$A$3:$A$30

where Sheet1 is Cameras, and Sheet2 is Monitors. I have noticed however, that when I select OK or APPLY, Excel truncates the range to $C$3:$C$30,$A$3:$A$30. In other words, it deliberately ignores the sheet name, and therefore applies the two ranges to the same (current) sheet! So, what to do...

No duplicates with this code in WorkBook
by use 1 of the items from the drop down, next time it dont appears

Please Login or Register  to view this content.

Kind regards
Leo

Thanks Leo,

That's a bit different to what I wanted, but works in a very tidy fashion! I was asking for a solution that only warned me (by using conditional formatting to turn the relative duplicated cells red) about duplicates, but I also like a solution that prevents you from selecting a pre-selected IP address from the drop down list. Very nice. I tried your demo spreadsheet (thanks a lot for that) and found only one flaw though. If I free up an IP address, it is now missing in the list of IP addresses, so I can never select it again... How can we add the missing IP address back into the list?

Sorry I would do this myself, but I'm not really up to speed on writing script in Excel, though I do come from a programming background (Delphi and embedded programming).