help with vlookup

[milanko]

Hi Guys,

I have 3 sheets in workbook.
Sheet 1 – I import data from report, there is list of names and other stuff. I need to send email to some of the ppl on the list. When I do this I enter Yes in the column next to name and then I update the sheet. Names are transferred to sheet 2. In Sheet 3 – there is a table with a list of all the names. Once there is yes next to the name in sheet 2 – table is automatically updated and next to the name there is a number of how many Yeses has each name.
I would like to create a Vlookup formula that would automatically select and highlight/color names on Sheet 1 (names are in column F) that have 0 Yeses in the Sheet 3 (column B).
Please see simple excel. Can you help. Thx.

[benishiryo]

hi milanko. to reference another worksheet, you must first name the range for Sheet3, A2:B6. you can check out this video:
http://www.youtube.com/watch?v=0BXka20-nOk

so instead of naming the whole column A in the video, you name that particular range i mentioned. and since you are using Excel 2003 & dont have access to the ribbon where he clicked on Conditional Formatting, try to press ALT + O + D to get into the window. use a custom formula:
=VLOOKUP($F2,Sheet3!$A$2:$B$6,2,0)=0

[jdpeterson72]

Enter this formula in a new column on sheet 1:

=VLOOKUP(F2,Sheet3!$A$2:$B$6,2,FALSE)

The first variable, "F2", is the common field between the two sheets.
2nd variable is the table you want to pull your result from - it needs to include the field you are keying on (F2) through the result.
3rd variable is the number of columns (inclusive) between the field you are keying on and the result. In this example, its the 2nd column in the table.
4th variable - always enter "false".

Make sure the range A2:B6 referenced on sheet 3 captures your entire table. Copy the formula the length of your table (make sure to include the absolute references as depicted above with a "$").

[gwsampso]

the attached should do the trick although it might be a little bit over the top

TESTING.xlsm

to run enable macros and click button

if your list in sheet 2 is over 1000 rows you will have to make some adjustments im sorry