How to write formula for below problem (Conditional Formatting)

[vijay.vizzu]

Hi all,


Below is my problem, i have tried to write a formula for conditional formatting, but i can't able to write, becoz conditional formatting will only executive TRUE condition, here in my problem there are TRUE & FALSE, how to write formula, i don't know.


Problem: I have an excel sheet, around 46 columns, now i want to put CF on A2:AT100, now my condition is, first to be compare with below cell (A2 compare with A3), wherever it not matched then it should executive the CF. It is very simple. But in my case it is =IF(LEFT(A2,1)="9",LEFT(A2,11),MID(A2,5,5) to get values (if the cell contents start with 9, it should consider 11 digits otherwise mid of 5 chars) then same formula for below cell. If they are match, it should apply CF otherwise leave it


I hope, you got my point. Currently i am using helper column to do this, but everytime i need to update the cells whenever i inserted a row between the data.


Any help would be appreciated.


Regards

vijay

 

[Hui]

Vijay


Can you post a few examples of when it should be True and when it should be False

So we can workout a formula for you

 

[Faseeh]

Hi vijay.vizzu,


The first problem that i can see in your formula is that you want using LEFT(A2,11) to calculate the length, instead it should be LEN(A11)=11.


Secondly, upload a sample file and explain True/False Process with it.or if your data is in A2:B4, is the logic of following formula correct?

=IF(LEFT(A2,1)=9,A2=A3,MID(A2,5,5)=MID(A3,5,5))

Regards,