Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Prevent duplication across sheets
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Prevent duplication across sheets
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" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Prevent duplication across sheets
That's clever, I like that. Have to remember that one. But, you're right,
it probably won't speed things up. Thanks anyway! "Toppers" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent certain tabs/sheets from refreshing | Excel Discussion (Misc queries) | |||
prevent user to delete specific sheets | Excel Discussion (Misc queries) | |||
How-to Prevent Value Duplication | Excel Discussion (Misc queries) | |||
How do I prevent duplication of numbers in a column | Excel Discussion (Misc queries) | |||
prevent duplication | Excel Discussion (Misc queries) |