Search columns on multiple worksheets

W

wally

In a workbook I have a worksheet for each month of the year. How would I be
able to automatically determine whether the entry currently being made has
been entered in the same column previously on any of the worksheets?
(Similar to “Find†under Edit) If so, would it be possible to indicate the
previously entered data by highlighting each duplicate cell?
 
S

ShaneDevenshire

Hi,


Go to each of the other sheets and highlight the range where the data is,
suppose B1:B100. In the Name Box, left side of the Formula Bar, enter
FirstColumn and press Enter. Move to the next sheet and do the same by give
it the name SecondColumn.

Highlight the cells where you want the formatting to alert you to
duplicates. Choose Format, Conditional Formatting, and pick Formula is from
the first drop down. In the second box enter the formula

=OR(B1=FirstColumn,B1=SecondColumn)

Click the Format button and choose a color on the Patterns tab, click OK
twice.
 

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

Auto fill multiple worksheets 5
Naming ranges on multiple worksheets 3
Excel Vba to change displayed year automatically. 14
Countif in multiple worksheets 6
Combine Data from several worksheets 1
VLookup Function over Multiple Worksheets 3
Modify RDB code with find and offset 6
Auto copy data rows between sheets depending on date entry 4

Top