Searched and not sure to use vlookup, or match, or conditional formatting?

[Frau10125]

Here is my issue. I have a list of student names on one excel sheet, and I have to input names on another list. What my boss would like is to highlight the names on the 1st list if they show up on the 2nd list.


FYI - The fist list is static. The second list would be updated daily. Would a macro also need to be created for this?


Thank you!

 

[Hui]

Frau10125


Firstly, Welcome to the Chandoo.org forums.


Conditional Formatting is your friend here


Select the 1st list, I'll assume it goes from A2:A10


use a formula like: =Countif(List2, A2)>=1

Then set your format


If List2 changes in size every week, set it up as a Dynamic Formula

 

[Frau10125]

Thank you for the welcome!

Let me see if I can post some pictures or clarification, because what you said to do just messed up the list I had or I'm just not understanding. Thank you for being patient!


so on List 1, cells A3:A10 look like this:

[pre]

A        B               C
00117	Brad 	        Allen
00053	George	        Berger
00050	Nicholas        Berger
00196	Bradley	        Cooper
00003	Christopher	Meloni
00002	Augustus	Goope
00058	Matthew	        Cook
00117	Nicholas	Cage
On List 2, which is in the same excel sheet just on a different tab, I have it as such

cells A3:A9 (so far) (column A is the date)

B      C           D
00111	Park	  Benjamin
006149  Croft	  Laura
000077	Durberville Tess
001151	Great	  Alexander
001103	Simone    Hannah
000564	Goode	  Matthew
00196	Cooper    Bradley

[/pre]
On the second list I would have typed all these names and id numbers on a daily basis, how can I have it highlighted on the 1st list that Bradley Cooper's name showed up? Mostly by his ID number since that would be more unique than his name, and I apologize that I don't know what a dynamic formula is..


Thank you!

 

[Luke M]

With list 1, let's say you select cell A2. CF formula is:

=COUNTIF(Sheet2!$B:$B,$A2)>=1


you can then copy this formatting to the right and down as needed, and all the formatting will stay dependent on what is in the ID column. This is due to the use of the dollar signs making it an absolute column reference.

 

[Jai9]

Hi Luke,


According to the post, the two lists are on different sheets in the same workbook.


Conditional formatting doesnt seem to work between 2 different sheets.


Is there any workaround for this?


Jai

 

[shrivallabha]

Named Ranges can be used in conditional formatting.


So create a named range on first sheet say which refers to the list to check:

Emp_List


and then check on the second sheet (e.g. Cell B2) using:

=ISNUMBER(MATCH(B2,Emp_List,0))

 

[Luke M]

Bah, good catch Jai. Will need to use a named range. (Don't know why Microsoft hasn't gotten rid of this yet).


On Sheet2, select column B, and give this the name "MyRange" (either rusing the Name Manager or typing directly in the box to the left of forumula bar).


Then, back on Sheet1, in the CF formula, you can use:

=COUNTIF(MyRange,$A2)>=1


Just like "magic", XL will not let you use conditional formatting rules referring to another sheet. For future note, sam trick can be applied to Data Validation.

 

[Jai9]

Thanks Luke and Shrivallabka,


It worked as expected.


Jai

 

[Luke M]

Glad we could help get things back on track. Have a great weekend!