Forum Discussion
Conditional Formatting Formulas
mje131 Let me see if I understand you correctly. Let's say you have the letters A-Z in rows 1-26 and you want to have conditional formatting for rows 1-13 to highlight vowels (A, E, I, O, U) and rows 14-26 to highlight cap letters using 2 strokes/lines (I'm defining them to be -> D, J, L, P, Q, T, V, X). So rows 1, 5, 9 (A, E, I) would be highlighted from 1st rule and rows 16, 17, 20, 22, 24 (P, Q, T, V, X) are highlighted based on 2nd rule. Then you re-sort the list in reverse order Z -> A. The conditional formatting will now highlight letters U, O, L, J, D. If that is what you want then you are all set. If, however, you want A, E, I, P, Q, T, V, X to still be highlighted even though they moved into a different rule range then you need something different.
If the latter is the case, then in order to do that you will need another column (or use an existing column if one exists) that will help define the original range. Having another column that is simply numbered 1, 2, 3, etc.... would work (note that column must be values and can NOT be a formula like =row() since the formula result will change after sorting) and then in Subodh_Tiwari_sktneer formula just reference that column instead of using row(). In my example you could actually use the alphabet itself since it was originally in order from A to Z.
Appreciate the quick reply. Yes, I think you are understanding my question based on your response. I believe the later is the case per you reply and is exactly what I'm looking for. Please reference attached. It shows the formula I typed into my file along with the error message I'm receiving. My guess is that it's something simple in my formula that I need to update but can't figure it out. Any additional help would be appreciated. Thanks,
mje131 The format of your if statement is incorrect. The correct format is:
IF( [conditional statement], [statements if true], [statements if false] )
so based on what you tried I think this might be what you are trying for:
=IF($C:$C<370,AG5<SUM(AH5+AL5+AP5+AT5+AY5+BD5), AG5<SUM(AH5+AL5+AT5))this is fine if you are creating a column and paste this in row 5 of the new column BUT if this is going in the conditional formatting custom formula field you will have a problem. Your original post talked about F2 and F7 but this formula looks like it is intended for row 5. assuming the conditional formatting is for a range like F2:F100 then the formula should be:
=IF(C2<370,AG2<SUM(AH2+AL2+AP2+AT2+AY2+BD2), AG2<SUM(AH2+AL2+AT2))notice that the custom conditional formula is all based on the upper left cell of the range it is being applied to. As it progresses cell to cell it will adjust the formula accordingly so any cell reference part that shouldn't change needs to have a '$' in front of it. For example if this formatting was to be applied to the whole row (e.g. A2:AZ100) then the formula should be:
=IF($C2<370,$AG2<SUM($AH2+$AL2+$AP2+$AT2+$AY2+$BD2), $AG2<SUM($AH2+$AL2+$AT2))as you can see each column reference is preceded by a '$' but the row references (in this case they are all referencing row 2) does NOT have a '$' in front of it so when looking at a cell in row 3 excel will auto increment that reference to row 3.
best of luck
I think I'm close but still haven't figured it out. I don't think I've explained what I'm looking for clearly so let me try again. I've also change this sample file a little to hopefully make it easier. Please reference attached file and reference the conditional format formula I have in cell AG2. I've added column C to help since I did not have this column originally. Instead of numbers which could cause confusion I've chose to just utilize an "x" or blank cell in column C.
My goal is to have the cells in column AG shade red via conditional format if that value in the cell is lower than either 6 months or 3 months out (hence I have formula adding all the Eoh columns to the right to see if the value in AG falls below that quantity).
I want to change how many months I look at for this given exercise (either 3 months or 6 months). So I've chose to mark an X in column C to tell the formula to look out 6 months or leave it blank if I want it to look out only 3 months.
The formula I have in cell AG2 seems to work but I still don't think it's correct because when I apply this same formula to my true master working file it doesn't work. It's still shading cells, but not correctly so that tells me I don't have the formula correct.
I certainly appreciate the help so far and hope you can assist with this question as well. Thanks,
mje131 The formula for what I think you want seems to be correct, but when moving the formula to another sheet you need to be careful that the cell references are still correct.
That said and assuming you will want this same basic formula for each month's column (e.g. AK, AO, AS, ...) a different formula might make your life easier. Let's assume this formula is applied to AG3:ZZ999 (or whatever the end range is):
= (RIGHT(AG$1,3)="Eoh")*(AG3<(AH3+AL3+AP3+($C3="x")*(AT3+AY3+BD3)))
so the first thing you might notice it there is no IF() statement. If the formula evaluates to anything other than 0 it is considered true and hence I multiply the conditionals (FALSE=0). I could have also chose to use AND().
Next, the first part of the formula checks if the column heading ends in "Eoh"
The last part checks for AG3< 3 months and adds another 3 months if $C3 has an "x"
NOTE the '$' in front of the "C" in "$C3" so that no matters what cell Excel is evaluating in the range it will always look at column "C". Same goes for the $1 in AG$1 at the beginning of the formula to always look at row 1 to check for the header information.
Also note the whole formula is based off of AG3 as that is the upper left of the range I mentioned. If that range changes you may have to adjust the formula accordingly, but Excel will usually do that for you (but sometimes it doesn't work right)
As for why it isn't working on your master sheet, I can't really say without looking at that sheet but hopefully these tips will help you understand how it works better and you will find the problem (and maybe even make a better formula in the process).