I am working with an Excel workbook that contains two sheets. On the first sheet, I have several columns of information about departments participating in a certain program, including names and email addresses. On the second sheet, I have several columns of information regarding correspondence with the departments, including names and email addresses. I would like to create a conditional format in the cells of one column of sheet 2. Either the column containing the names or email addresses, but preferably the email addresses.

I would like to have the formula look at the email address in the cell and find it in the range on the 1st sheet then, if it is not there, format the cell to fill with a certain color. Basically I would like to say, "if this email is not on the first sheet, fill in with yellow". It looks like I can’t do this because: "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."

So, if it can’t be a conditional format, I would like to create a column in sheet 2 just to tell me if there is a match. I would like the formula to look in a cell on sheet 2 and find the same email address in a certain column on sheet one & return a "no", if there isn't one, or a “yes” if there is one, or something that would tell me one way or the other.

Is this possible? If so, what is the formula I would use?

re: "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria."You can reference cells in another sheet if you use a Named Range.

Does that help or do you need more detail to solve your problem?

Well, that is helpful to know, but I believe that I also need to know what formula to use. Thanks so much!

Try VLOOKUP... Name the range that contains the email addresses on Sheet1.

I'll call it emailadds.

Select the range of cells that contain the email addresses on Sheet2. Let's say it's A1:A20. Select that entire range.

Format...Conditional Formatting...Formula is...

=IF(ISNA(VLOOKUP(A1,emailadds,1,0)),TRUE,FALSE)

This will Conditionally Format each cell in the range with it's correct cell address.

The VLOOKUP will look up the email address in Column1 of the range named emailadds. If it doesn't find it, it will return #N/A and that will cause the IF statement to return TRUE and format your cell.

Wonderful! Thank you so much!

I am about to leave for the day, but i will try this tomorrow.

I appreciate your help!

No! You can't leave. You have to try it now!I won't be able to sleep unless I know I solved your problem.

Haha! Okay. Hang on . . .

I was kidding. Go home!

Well, I tried it and nothing happened.

I will have to see if I did something wrong.

I'm not sure I understand the formula.

Iamleaving now.

:o)

I'll let you know tomorrow.

Thanks again!

For the morning.... =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_valueis whatever it is that you are looking up.In my example, that would be whatever is in A1, for A1, whatever is in A2 for A2, etc.

table_arrayis the range of cells that VLOOKUP will look up thelookup_valuein, and return a value from if thelookup_valueis found. VLOOKUPalwayslooks in the first column of thetable_arrayfor thelookup_value.In my example, the

table_arrayis the range I named emailadds.

col_index_numis the column number of thetable_arraythat VLOOKUP will return a value from if it finds thelookup_valuein the first column of thetable_array.In my example, I am asking VLOOKUP to return the value in the first column of the

table_array, but that doesn't really matter as I'll explain later.

range_lookuptells VLOOKUP whether to look for an exact match or not.In my example, I used "0" which means I want VLOOKUP to find an exact match.

If VLOOKUP does not find the

lookup_valuein the first column of thetable_array, it will return #N/A.Excel provides provides a function that allows you to check for a #N/A error which is =ISNA(). It's often used with VLOOKUP and an IF function to prevent the display of the #N/A error. For example,

=IF(ISNA(VLOOKUP(something)),"Not Found",VLOOKUP(something))

What this will do is return "Not Found" if VLOOKUP can't find what it is looking for or it will return the correct value from the

table_arrayif thelookup_valueis found. "Not Found" is prettier than #N/A.So, when using VLOOKUP for Conditional Formatting, we don't really want it to return a value, we just want to know if the

lookup_valueis found or not.If the

lookup_valueis not found, it will return #N/A, which will make the ISNA() function TRUE, which in turn will make the IF statement TRUE which should should format the cell with whatever formats you choose via Conditional Formatting.You did choose a format to apply to the cells didn't you? If you didn't, nothing will happen even if the IF statement evaluates to TRUE.

BTW...you can test the formula in a cell and it will return TRUE or FALSE depending on whether or not the

lookup_valueis found. TRUE means thelookup_valuewasnotfound.Hope that helps...

YES! That's great! Thank you so much! And thanks for the explanation, too! I sincerely appreciate your time and willingness to share your knowledge.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History