Conditional Formating

[Jerk24]

What formula is needed to Highlight only even numbers. </SPAN>

the trick is I have the following formula</SPAN>

=CONCATENATE('INDV Log'!AG41," ",'INDV Log'!AH41)</SPAN>

the result is</SPAN>

TEAM 2</SPAN>

So there are 12 Teams and I just want to highlight the even teams with conditionally formatting or some other way??</SPAN>

 

[Joe4]

Indeed. But the cells showing "Team 2" is just a concatenation of two other cells. So the effect of my method is to just reference the source cell - from the OP question, it was 'INDV Log'!AH41. Why sanitze the concatenation back into a number value when you could just refer to the source?

Ahh. I see where you are going with that. If the formula is on the same row as the other cells it is calling, that makes good sense (i.e. row 41 in the example). If not, it could get a little tricky.

 

[bruderbell]

True. My unstated assumption, then, is that the source data is congruous with the concatenated values. Even if the source data is 4 columns left, down 17 rows, and on a different sheet, the conditional formatting rule will work as long as the data is shaped the same.

 

[Jerk24]

Joe4

The value that are being highlighted from the formula are, Team 2 - 8. it is not recognizing team 10 and 12.</SPAN>

 

[Joe4]

The value that are being highlighted from the formula are, Team 2 - 8. it is not recognizing team 10 and 12.

I am not quite clear what you are saying here.
Are you saying that the entry in cell B4 is "Team 2 - 8"?
Are you applying the conditional formatting to all the other cells?
Since we cannot see your spreadsheet, we are really at the mercy of what you tell us.

So let's approach this another way. Pick an example of one that is not working, and answer the following questions:
- What cell does the value that isn't working in reside?
- What is the value being return by that cell?
- Highlight that cell, and go to Conditional Formatting, and type the Conditional Formatting formula, exactly as it appears, here.
- What is the length of the value in your cell (i.e. if it is cell B4, what does =LEN(B4) return)?

Please answer all 4 of these questions for us.

Thanks

 

[Jerk24]

Okay.

Cell B4 currently has Team 2 with the formula (=CONCATENATE('INDV Log'!AG41," ",'INDV Log'!AH41) Which filled down to B15 ending with =CONCATENATE('INDV Log'!AG52," ",'INDV Log'!AH52)

B5 - Team 9
B6 - Team 3
B7 - Team 8
B8 - Team 5
B9 - Team 4
B10 - Team 7
B11 - Team 10
B12 - Team 12
B13 - Team 11
B14 - Team 1
B15 - Team 6

The problem is when I use the Formula =Mod(mid(6,2,2),2)=0 the only highlighted cells are B4,7,9,15

the cells with Team 10 and Team 12 also should be picked up since they are even.

I hope this helps.

 

[Joe4]

Well, you kind of half-answered the questions I asked you in the last post. So let's focus on the entry in B11.

Please do the following two things, exactly as I am intructing below:
- Highlight cell B11, go into Conditional Formatting, Manage Formulas, and tell us what it shows the formula (exactly how it is written).
- Pick any blank cell, and enter the following formula and tell us the result: = LEN(B11)

 

[Jerk24]

The Conditional Formatting in Cell B11 =MOD(MID(B4,6,2),2)=0

The Results of the Formula Len(B11) in a blank Cell is 8.

 

[Joe4]

Well, you uncovered two potential problems right there:

The Conditional Formatting in Cell B11 =MOD(MID(B4,6,2),2)=0

Why is the Conditional Formatting formula in cell B11 referencing cell B4? It should be referencing cell B11!
If you highlight your whole range for Conditional Formatting at once, and write the formula as if you were applying it to the first cell in the range only, the formula for all the other cells should adjust automatically.

Similarly, you could just highlight one cell and write the Conditional Formatting formula and condition for that cell, then use the Format Painter to copy that formatting to other cells.

The Results of the Formula Len(B11) in a blank Cell is 8

Why is it saying the length of "Team 10" is 8 spaces? It should be 7.
It probably means that you have an extra space in there somewhere (at the beginning or end, maybe?). Take a look at the source of these cells (used in the Concatenation) and see if you have an extra space in there somewhere.

 

[Jerk24]

Thank you. I had three spaces betweenthe word Team and the Number.

Genius. thanks and sorry for the early confusion.