I need to know how to check duplicates in a workbook with several worksheets. Basically making sure there are no repeats in times that are entered into the buisness form for each sheet. There could be up to 10 sheets in the workbook and would be wonderful to be able to check them all quickly for duplicates. Thank you for your help!
Custom functions in Conditional Formatting cannot reference cells in other worksheets in the same workbook, and cannot reference cells in other workbooks. However, you can get around this limitation by using defined names. Create a "defined name" which refers to the list in the other workbook or worksheet, and then use that name in your custom function.
For example, suppose you want to make cell A1 on Sheet1 red if that cell's entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an error message from Conditional Formatting. To get around this error, create a defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and use the name in your custom formula:
I will create a video in the near future which will show the "defined names" work around which can allow highlighting duplicates in mulitple sheets.
aqueity 7 months ago
I need to know how to check duplicates in a workbook with several worksheets. Basically making sure there are no repeats in times that are entered into the buisness form for each sheet. There could be up to 10 sheets in the workbook and would be wonderful to be able to check them all quickly for duplicates. Thank you for your help!
MsVansley 7 months ago
Custom functions in Conditional Formatting cannot reference cells in other worksheets in the same workbook, and cannot reference cells in other workbooks. However, you can get around this limitation by using defined names. Create a "defined name" which refers to the list in the other workbook or worksheet, and then use that name in your custom function.
MrTmcbath 7 months ago
For example, suppose you want to make cell A1 on Sheet1 red if that cell's entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an error message from Conditional Formatting. To get around this error, create a defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and use the name in your custom formula:
=COUNTIF(MyList,A1)=0
MrTmcbath 7 months ago