Conditional formatting - identifying blanks cells based on another cell entry

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
185
Hello Friends,

I am looking for an easier way to use a formula that I have in a broader sense. I currently use the formula in a column aspect which works well, however, I don't want to have to repeat the same thing 12 times to achieve my goal. The formula that I am using looks like this. =AND(COUNTIF(D4,""),COUNTA(B4)). This formula covers several cells in the B column. That same formula is used in column D just the B4 and D4 are switched. So for the above formula, I would have to do it 12 times to cover each month, D4,E5,E6 M6,N6.O6 V6,W6,X6, AE6,AF6,AG6 for it to work.

Added, for the switch version, I would have to do it for each month. Is there a better way to do this where it does not have to be entered so many times. Perhaps an array of sorts.
Please see the link below for an example version.. Columns B and D have the condition formatting. Thank you

 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,613
Am I to assume that you want the cells to turn red if the user has not fully completed all 3 months of data?

If the HR Advisor has data, then there must be values in each month
If the any of the months has a value, then the remaining months and the HR Advisor must have values?
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,613
I have two formulas for you to try

This one tests all 4 cells and determines that if any are not empty and not all of them are full then turn red
=AND(COUNTA($B4,$D4:$F4)>0,COUNTA($B4,$D4:$F4)<4)


This one tests if B is not empty and the month cells are not complete | along with if B is empty and the month cells have at least one value, then turn red.
=OR(AND(COUNTA($B4)=1,COUNTA($D4:$F4)<3),AND(COUNTA($B4)=0,COUNTA($D4:$F4)>0))

Using anchors ($), you can have it test multiple columns. Hope that gets you closer. This can be used in one conditional formatting so you don't have to create a CF for each column.
 

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
185
I attempted to use both but not sure if I understand them or how the works overall. Maybe I am using it incorrectly. When I did use it everything turned red.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,613
They are not to used together. When I use either, they work as described. When you say everything turned red, do you mean all the lines turned red even if the criteria wasn't met?

The first CF formula counts all values in columns B, D, E, and F. If any are not blank, but some are blank, then turn red.

The second CF formula has a two part test:
1: If B is not blank and any of D, E, and F are blank, then turn red
2: If B is blank and any of D, E, and F are not blank, then turn red.

For your affected range, you have the option of choosing any of the columns mentioned because of the anchoring. You do have to start your affected range on row 4 because that is the base row for the CF formula.
 

Newbienew

Board Regular
Joined
Mar 17, 2017
Messages
185
I went thru and did the condition formating for all the columns and cells. Hopefully this will explain it better. I wanted to avoid doing the CF repeatedly as i did. I attempted to use your CF formula but struggled with trying to understand it. I believe the second formula would be what i need but not sure how to use it i guess. Conditional Format issue

 

Forum statistics

Threads
1,085,372
Messages
5,383,262
Members
401,820
Latest member
RustEE2020

Some videos you may like

This Week's Hot Topics

Top