Conditional formatting restricted to first column

[Birdbrain1463]

So I'm pretty sure this is a simple fix but it has got me stumped. I have a data table for which I am trying to run conditional formatting for a whole row when particular criteria is met in the last few columns of the table, however the formatting is only affecting column A.

I am using the "Format based on a formula" function and the formula I am using is as follows:

=OR(AND(NOT(ISBLANK($Q2)),$Q2<S$1,ISBLANK($R2)),AND($P2="Frequently",$Q2<(S$1-7)),AND($P2="Monthly",$Q2<(S$1-23)),AND($P2="Quarterly",$Q2<(S$1-87)),AND($P2="Major Dev",$Q2<(S$1-180))) - I appreciate this might not be the most concise way of writing the formula, I'm afraid my current skills with excel are limited!

Within the "Applies to" box I've put =$A$2:$R$480 (starting from 2 to discount the column titles) to cover the whole table and have also tried just $A:$R but both have the same effect of only applying the formatting to column A. I've also tried changing it to $B:$R and that results in the formatting not showing up at all, so it appears to be something wrong with columns B onwards that just aren't displaying the formatting.

I'm not sure whether there's an option somewhere I've selected that restricts formatting in this way? I've had a look on various help guides but they have told me to set up the formatting function exactly as I have done already.

I have also tried running another conditional formatting rule as a test (format only cells containing the letter "a") and this is working fine, so it's not an inability of the other columns to display formatting, just that for some reason they're not in this case.

What's weird about it is that column A doesn't even feature in the formula I'm running. I would understand more if it was only formatting columns P, Q and R, but it's just column A

Thanks in advance for whoever can help with this, it's been frustrating me more than a little!

 

[Birdbrain1463]

So I'm pretty sure this is a simple fix but it has got me stumped. I have a data table for which I am trying to run conditional formatting for a whole row when particular criteria is met in the last few columns of the table, however the formatting is only affecting column A.

I am using the "Format based on a formula" function and the formula I am using is as follows:

=OR(AND(NOT(ISBLANK($Q2)),$Q2<S$1,ISBLANK($R2)),AND($P2="Frequently",$Q2<(S$1-7)),AND($P2="Monthly",$Q2<(S$1-23)),AND($P2="Quarterly",$Q2<(S$1-87)),AND($P2="Major Dev",$Q2<(S$1-180))) - I appreciate this might not be the most concise way of writing the formula, I'm afraid my current skills with excel are limited!

Within the "Applies to" box I've put =$A$2:$R$480 (starting from 2 to discount the column titles) to cover the whole table and have also tried just $A:$R but both have the same effect of only applying the formatting to column A. I've also tried changing it to $B:$R and that results in the formatting not showing up at all, so it appears to be something wrong with columns B onwards that just aren't displaying the formatting.

I'm not sure whether there's an option somewhere I've selected that restricts formatting in this way? I've had a look on various help guides but they have told me to set up the formatting function exactly as I have done already.

I have also tried running another conditional formatting rule as a test (format only cells containing the letter "a") and this is working fine, so it's not an inability of the other columns to display formatting, just that for some reason they're not in this case.

What's weird about it is that column A doesn't even feature in the formula I'm running. I would understand more if it was only formatting columns P, Q and R, but it's just column A

Thanks in advance for whoever can help with this, it's been frustrating me more than a little!

P.S. Sorry for saying "formatting" so much

 

[jasonb75]

What is in the cells that you're testing with ISBLANK()?

The ISBLANK function is badly named and should really have been called ISEMPTY. If the cells contain formulas that show a blank by using "" then ISBLANK will return FALSE.

Also, noting that some of your criteria use things like $Q2<(S$1-3) if Q2 is a formula blank then it will be greater than any numeric value. If it is empty (no formula) then it will be less then any numeric value.

 

[Birdbrain1463]

What is in the cells that you're testing with ISBLANK()?

The ISBLANK function is badly named and should really have been called ISEMPTY. If the cells contain formulas that show a blank by using "" then ISBLANK will return FALSE.

Also, noting that some of your criteria use things like $Q2<(S$1-3) if Q2 is a formula blank then it will be greater than any numeric value. If it is empty (no formula) then it will be less then any numeric value.

The data table is to log a record of contacts to a list of potential clients. In column Q I record the date in which a contact attempt was made and in column R I record whether I was able to successfully connect, so neither are governed by a formula, but column Q is set up to record any date on dd/mm/yyyy format.

The focus of the format I am trying to create is to provide a visual prompt (highlight the row entry yellow) when I need to re-connect with the client, hence why I do not want it to highlight clients for which column Q is blank as the prompt is to maintain the client relationship rather than starting a new one.

The $Q2<(S$1-3) is to track when the last attempt to contact exceeds a particular date. In cell S1 I have the formula =TODAY()-7, and it is off of the resulting date that I track how long it has been since I last attempted to contact a client.

I've attached a screenshot of the breakdown I used in writing the formula for reference.

The only cell on the worksheet governed by a formula is S1, all of the cells within the table are either blank or simply have text input.

I don't think it's a formula issue though as the formatting is functioning correctly (i.e. it's obeying all of the rules I've listed in the screenshot attached), however the end result of the highlighted cells is only affecting column A rather than columns A-R.

 

[Fluff]

It sounds as if you need to lock cell S1 like $S$1 rather than S$1

 

[Birdbrain1463]

That's not fair, how is it that simple a fix!

Thanks very much, it's been driving me up the proverbial wall!

Out of interest though, what's the logic behind that do you know? It seems like such a small change for such a big difference.

I also had to change all of the cell references to 1 rather than 2 in order to get the formatting back onto the correct records, just FYI if anyone reads this at a later date and has a similar problem.

 

[Fluff]

It only worked for col A because that column was looking at S1, but col B would be looking T1, col C at U1 etc
Also the Q2,R2 etc must be the same as the first row in the applies to range, So with your original range of $A$2:$R$480 they would have been correct.

 

[DCard]

It sounds as if you need to lock cell S1 like $S$1 rather than S$1

THANKS VERY MUCH. that was basically te fix for my annoying prob.

in this case, i just changed to $al7 and that worked like a charm

=ISNUMBER(SEARCH($AL$2,$AL7))