Forum Discussion
How to highlight the cells using a formula with conditional formatting or else
If you get the formula displayed like this, then your Excel version does not yet support SCAN(). Excel for the web already does, so it also works on the i-Phone.
=SCAN(,INDIRECT("A2:A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),LAMBDA(a,b,IF(OR(b="",b="Subtotal"),a,b)))I have now modified it so that it should also work on older versions of Excel. If you have more than 1000 entries, you have to extend the formulas in the sheet as well as in the conditional formatting.
dscheikey
Hi,
When I downloaded the file on my iPhone it opens up and I see the formula but when I downloaded the file on my laptop it spills an error in the formula (the error is inside the screenshot attached). I looked up the error and it gives me that specific office version does not support all functions. I am with Office 365 Enterprise (also screenshot attached). I believe that it is working but cannot check it. Can you paste the formula from D2 here so I can try and copy paste it in the excel on my laptop.
Thank you very much!
If you get the formula displayed like this, then your Excel version does not yet support SCAN(). Excel for the web already does, so it also works on the i-Phone.
=SCAN(,INDIRECT("A2:A"&LOOKUP(2,1/(A:A<>""),ROW(A:A))),LAMBDA(a,b,IF(OR(b="",b="Subtotal"),a,b)))I have now modified it so that it should also work on older versions of Excel. If you have more than 1000 entries, you have to extend the formulas in the sheet as well as in the conditional formatting.
Hi again 🙂
I've reviewed the last excel you've attached and changed the values in B66,67 and 68 to Test3 in order to test if it will make it green as we have at least 3 times Test1, Test2 and Test3 but it's not working.
Can you please check the attached worksheet. I didn't change anything else. I don't know if formula 3) is compromised from Formula 2) if it finds the number inside sheet1.
Thank you!I think it is just a small error. Please change the second conditional formatting at the very back from "2".
=AND(COUNTIFS($D$2:$D$1000,$D2,$B$2:$B$1000,"Test1")>2,COUNTIFS($D$2:$D$1000,$D2,$B$2:$B$1000,"Test2")>2,COUNTIFS($D$2:$D$1000,$D2,$B$2:$B$1000,"Test3")>2)Sorry for a small careless error.
THANK YOU VERY MUCH!!!
I cannot thank you enough....how can I promote you or send some kind of thanks in this forum? I hit the Best Response button but couldn't find anything else?
🙂