Solved

Conditional Formatting

Posted on 2011-09-13
2
313 Views
Last Modified: 2012-06-27
Hi Experts,

I would like to request Experts help create a conditional formatting to cross check data in column B (Source_ sheet) with data in column B (Data_sheet). The data from Source sheet (Column B)  are generally listed at the end of the list in column B in the Data Sheet.

If the data on the source sheet (Column B) not available at Data sheet, the cell need to highlight with "Red" color. Please ignore data starts with "uration" and "------" during crosschecking process. Hope Experts could help me create this feature.

 
Track-Data.xls
0
Comment
Question by:Cartillo
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36533766
In Excel 2010, I was able to create a formula-based CF rule for Source!B1:B457 using:

=AND($B1<>"",$B1<>"------------",$B1<>"uration",COUNTIF(Data!$B:$B,"*"&$B1&"*")=0)

That will NOT work in previous versions; before Excel 2010, a CF formula cannot refer to another worksheet.

In Excel 2007, and presumably earlier versions, this is a workaround:

1) Create a named range, DataB, referring to Data!$B:$B

2) Change the CF formula to:

=AND($B1<>"",$B1<>"------------",$B1<>"uration",COUNTIF(DataB,"*"&$B1&"*")=0)
0
 

Author Closing Comment

by:Cartillo
ID: 36534444
Hi,

Thanks a lot for the help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question