Solved Conditional formating using values in multiple columns

August 17, 2012 at 14:36:35
Specs: Windows XP
I am working with a spreadsheet containg columns of data like customer name, year, individual months. The data is downloaded from a sub-system and we must check every month if the sales figures for all the customers were downloaded. The sheet contains rows of data per customer for the past 6 years. The year and zeros are downloaded as text.

To determine if the customer's balance is zero for a month; value(mid($g1:g200,1,1)=0 (TRUE)
To determine if the month is in the current year: value(mid($c1:c200,1,4)=2012 (TRUE)

Both these conditional formats colour the required fields if executed individually, however joining them with a AND statement returns nothing:
AND(value(mid($g1:g200,1,1)=0, value(mid($c1:c200,1,4)=2012).

I have tried Absolute Values as well, without any success.

Could you please indicate what I am missing here?

Thanking you in anticipation.


See More: Conditional formating using values in multiple columns

Report •

August 17, 2012 at 15:57:24
✔ Best Answer
Well, there a few things I see in your post that don't make sense.

First, it does not appear that you copied your formulas directly from a spreadsheet, since they are not capitalized. When you type them into your post "freehand" there is always the possibility of a typo.

That might explain some things since the formulas you posted will not work:


Both of these formulas are missing parenthesis and therefore will never return TRUE

The correct syntax should be:


Note the additional parenthesis before the second equal sign.

Second, I'm not sure why you are using the MID function since your start_num is 1.

Why not just use LEFT?


Finally, are you trying to conditionally format individual cells based on the values in the corresponding rows?

Typically, when you are using CF, you select an entire range and then enter the formula for the first cell in that range and let Excel update the CF formula for all the rest.

For example, if I wanted to CF cells in A1:A200 based on values in C1:C200 and G1:G200 on a row by row basis, I would follow this procedure:

1 - Select A1:A200
2 - Enter this CF Rule:

=AND(VALUE(LEFT(G1,1))=0, VALUE(LEFT(C1,4))=2012)

When I do this, for any row where the 2 criteria in Columns C and G are met, that row in Column A will be formatted by the CF rule.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 17, 2012 at 22:28:01
Thank you very much, DerbyDad03!

The missing parenthesis in my posting was an oversite (posted around midnight). It was typed correctly in the CF and returned TRUE if I loaded it individually. The mid function I thought to be necessary because the date was posted as '2012 and a zero as '0; therefore text.

I have adjusted your feedback to only return the customers with zeros in the current month but with sales in the previous month:

=AND(VALUE(LEFT(G1,1))=0, VALUE(LEFT(C1,4))=2012, VALUE(LEFT(F1,1))<>0)

Much appreciated.

Report •

Related Solutions

Ask Question