Forum Discussion

Steiny88's avatar
Steiny88
Copper Contributor
Sep 01, 2022
Solved

How to highlight the cells using a formula with conditional formatting or else

Hi Team, I have a specific worksheet with bunch of unique lets say serial numbers. Each serial number has it's own description. The serial numbers are in Column A The description is in Column B...
excel
Formulas and Functions
  • dscheikey's avatar
    dscheikey
    Sep 04, 2022

    Steiny88 

    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.

Steiny88's avatar
Steiny88
Copper Contributor
Sep 04, 2022

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!

dscheikey's avatar
dscheikey
Bronze Contributor
Sep 04, 2022

Steiny88 

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.

Resources