Excel formula to search multiple sheets

Microsoft Microsoft excel 2007 (pc)
December 21, 2010 at 13:55:08
Specs: Windows 7
I need to figure out how to do this:

1. I have a workbook page with roster names (last name only) in column A.

2. I have 4 other workbook pages where the people from the roster are being placed for duties.

3. I need a search program within that workbook that will scan the names and compare them to the names on the roster page. If a person's name appears on another worksheet then their name box will turn yellow (showing they've already been assigned) and if their name appears two or more times their name box will turn red showing double assignments.

Anyone have any help with that? Thanks!

See More: Excel formula to search multiple sheets

Report •

December 21, 2010 at 14:26:23
You can do that with Conditional Formatting.

My example will only use 2 sheets (not 4) so simply expand the concept to get it to work for any number of sheets.

First, since Conditional Formatting cannot reference other sheets in it's formulas, we need to use named ranges.

Let's say my Roster is in Sheet1!A1:A5.

Lets' say the names I am searching for are in Sheet2!A1:A5 and Sheet3!A1:A5.

1 - Select Sheet2!A1:A5 and name the range. I used "mySheet2"
2 - Select Sheet3!A1:A5 and name the range. I used "mySheet3"
3 - Select Sheet1!A1:A5 and click on Format...Conditional Formatting...Formula is
4 - Paste this into the formula Field and choose a Red fill format:


5 - Click Add, Formula Is, and paste this into the new formula Field, choose a Yellow fill format:


What this will do is count how many times each name appears in the named Ranges. If the sum of the counts is greater than 1, the cell will turn Red. If it equals 1, it'll turn yellow.

Hope that helps!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

December 21, 2010 at 15:05:14
I will give it a go. Thanks so much for the help! :)

Report •

Related Solutions

Ask Question