Prevent duplication across sheets

G

Guest

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!
 
G

Guest

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.
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula across multiple sheets 1
COUNTIF across multiple sheets with unknown names 1
Repeat the titles across multiple sheets of a workbook. 6
COUNTIF single criteria across multiple sheets?? 2
Test for string across multiple cells/sheets... further questions 6
Comparing Across Sheets 1
COUNTIF across multiple worksheets 7
Consolidate Excel Data from multiple sheets into one 1

Top