Conditional formatting

[vittiglio]

Hi!

So, last week I asked for help in this matter and it got solved... but now the file has changed a little bit and I can't apply the same formulas shirleyxls gave me.

http://www.excelforum.com/excel-form...he-symbol.html


The goal is the same as in the other file. The difference is this file is bigger and as more columns.


Any help?

[shirleyxls]

Hi again,

I think your only problem is where you have set the "Applies to" range. If you go to manage rules and select "This worksheet" (under Show formatting rules for) you will see that you have the same formatting applied to lots of different sets of rows, and some of the have #REF errors. My suggestion would be to just keep one set of rules and apply these to all the rows you need as a single range.

If you're not sure what I mean, just reply and I will try to clean it for you.

[shirleyxls]

I also just noticed that your text in column B has a space in front of each word, so you would need to adjust the LEFT statements to take this into account.

[vittiglio]

Oh thanks! that really solved almost of the issue
I really don't know what I would be doing without you, i'm feeling really dumb x).

The "yellow" conditional part still doesn't work. I think that is because the cell where de + is doesn't have a + introduced by hand. I'll try to show you:

Capturar.PNG
ds.PNG


Is there a way to overcome this issue? Or should I try another parameter? Maybe if we put instead of "LEFT" (on the +) something like "positive numbers"?

[shirleyxls]

Ah so it's actually just a positive number. So instead of checking for the + sign, just check to see if it is greater than 0. So something like this:

=AND(LEFT($B13,1)="T",$C13>0)

Not "dumb", just learning We all learn more from our mistakes

[vittiglio]

Thanks shirleyxls!

[Tony Valko]

=AND(LEFT($B13,1)="T",$C13>0)

Tip...

If you omit the number of characters argument in the LEFT/RIGHT functions it will default to 1.

A1 = This

=LEFT(A1) = T

[vittiglio]

Capturar.PNG

thanks for the tip Tony Valko!

So, now this happened... I made the corrections to the "yellow" condition and it solved the issue, but then the "green" one got crazy... any ideas? I tried re-writing it and changing the words to " Remessa Comerciante" with 20 characters, but he still goes for the " Débito"...

[shirleyxls]

Hi, sorry for the delay - been away from the computer for a few days

Are you still stuck with this one?

I tried it on the last example you loaded and it works fine. The English version of the formula looks like:

=LEFT($B14,8)=" Remessa" (notice the space before Remessa, which I can't see if you have in your formula)

[vittiglio]

Hummm. This is weird. Let me try formatting everything again and I'll tell you if something changes.

Thanks

[vittiglio]

So I've tried everything you told me to and nothing seems to work.
The green one just doens't work properly.

I'm sending you the original file.

[popipipo]

Please Login or Register  to view this content.

Change this.

[vittiglio]

thanks popipipo !!

now it works. :D

Could you just explain me why so I don't make the same mistake again?

[popipipo]

The formula (=LEFT($B2;8)=" Remessa") and range (=$A$2:$G$16) should start in the same row

[vittiglio]

Thanks a lot to both of you!