Prevent duplication across sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glenn - 12 Sep 2007 15:30 GMT

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!

Reply to this Message

Toppers - 12 Sep 2007 15:56 GMT

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.

> 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
[quoted text clipped - 4 lines]
> method. I'm willing to do it with VBA if that might be the way to go. Any
> ideas out there? Thanks!

Reply to this Message

Glenn - 12 Sep 2007 18:40 GMT

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

> A 3-D lookup is possible BUT it will only add to your performance problems:
>
[quoted text clipped - 4 lines]
>
> A5 is your product.

Reply to this Message