Prevent duplication across sheets

[Glenn]

In Excel 2003, I have a user who would like to prevent duplicate product
numbers from being entered on any of multiple sheets in a file. Since
conditional formatting, countif, and vlookup don't work across a 3-D range,
the brute force method is doing multiple lookups, countifs, etc, one for each
sheet, for each product, on each of the sheets. However, these files are
already massive & slow (at least on our machines!), and I'd like a better
method. I'm willing to do it with VBA if that might be the way to go. Any
ideas out there? Thanks!

 

[Toppers]

A 3-D lookup is possible BUT it will only add to your performance problems:

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A100"),A5))<>1,"Duplicate
Product","")

"mySheets" is a named range containing a list of your sheets.

A5 is your product.

 

[Glenn]

That's clever, I like that. Have to remember that one. But, you're right,
it probably won't speed things up. Thanks anyway!