SQL type query in excel with conditional formatting?

Question

Hi people,

I've been struggling with this problem pretty much the whole day: I have a large excel sheet with 5 columns of data. I am trying to highlight cells with conditional formatting to make reading the document a bit easier but it would seem the conditional formatting tool only works with ONE (yes, one) condition. If it was a SQL table I'd do it somewhere along the lines of "search with select from col3 where col2=123 OR col2=1234 OR col2=12345" etc etc.

Is there any smart way of doing this? I have about a 100 highlights to do on this data so It'll take me a long time to do it by hand invidually. Please help an Excel idiot! Thanks!

Answer 1

You can use OR() to check multiple values within one condition:

=OR(A1=123, A1=1234, A1=12345)



If all the values have something in common (like in your example) something like this will work, too:

=LEFT(A1,3)="123"



If you want to highlight cells if they contain any value that can be found on a list... put the list somewhere on the sheet (Y1:Y100 say), and use:



=COUNTIF($Y$1:$Y$100, A1)>0



Excel 2000 will accept accept up to 3 separate conditions. If it finds a match on the first one, it will not check the 2nd or 3rd.



If you need more than 3 conditions, you can put formulas in spare column and use another column (say Z) which will look at the results from the other columns and return a boolean value which you then refer to with the conditional format. i.e. =Z1="yes".

Answer 2

You can use multiple criteria with a conditional format by setting the condition to Formula Is:



=OR(column()=123,column()=1234,

column()=12345)

Answer 3

Hi,



It sounds like you might be able to use the information in this tutorial:



How to shade every other row (PowerPoint presentation)

http://www.agentjim.com/MVP/Excel/Excel%20shade%20every%20other%20row.ppt



-Jim Gordon

http://360.yahoo.com/profile-i7JMeio7cqvhotIUwCzaJWq9