Automatic numbering of rows if there is some text in adjacent cell - with one exception

[Cubajz]

Hi,

as a beginer I need to ask for help - I can´t find anywhere on the web how to make this into formula or vba macro (which is automaticly updating numbering, if anything changes):

Automatic numbering of rows if there is some text in adjacent cell - with one exception - if there is text "XX" in adjacent cell, skip numbering of THIS ROW (leave cell blank) AND continue numbering.

I think the picture explains it better:

Bez názvu.jpg

Thank for any advice

[Pete_UK]

Put the number 1 in cell A1, then in A2 you can have this formula:

=IF(OR(B2="",B2="XX"),"",MAX(A$1:A1)+1)

then copy this down as far as you need to.

Hope this helps.

Pete

[JohnTopley]

With data starting in row 2 and a1 blank

in A2

=IF(LEFT($B2,2)<>"xx",MAX($A$1:$A1)+1,"")

Copy down

[Cubajz]

This works great! Thank you very much for your quick answer! I just had to change "," symbols for ";" symbols..is that problem of Office version or something else? Nevertheless thanks for solving this

[AliGW]

No, it's your locale: in Europe and many other parts of the world you use ;, but in the UK, America, Australia and a few other places, we use ,.

[Pete_UK]

In many European countries the comma is used as a decimal point, and so to avoid confusion in Excel formulae you have to use a semicolon ( ; ) to separate different terms instead of the comma used in the UK and US. Sorry, I forgot to point that out in my earlier post.

Pete

[Cubajz]

Hi, I have encoutred more chalange in my project

Idea is the same as before, only now I need to add one more exception.
This was already very helpfuly answered:
Automatic numbering of rows if there is some text in adjacent cell - with two exceptions:
if there is text "XX" in adjacent cell, skip numbering of THIS ROW (leave cell blank) AND continue numbering.
if there is nothing "" in adjacent cell, skip numbering of THIS ROW(leave cell blank) AND continue numbering.

And now I need to add one more exception:
if there is letter "o" in the beggining of the adjacent cell, skip numbering of THIS ROW (leave cell blank) AND continue numbering.


Does this make sense? Thanks for any help in advance!

[JohnTopley]

Using Pete's formula (post #2)

=IF(OR(B2="";B2="XX";LEFT(B2)="o");"";MAX(A$1:A1)+1)

[Cubajz]

Thank you!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Cubajz]

One more question Is there a way to make this formula look only for small letter "o" and not for big letter "O"?

=IF(OR(B2="";B2="XX";LEFT(B2)="o");"";MAX(A$1:A1)+1)

It is the last formula posted in this thread, I have encoutered problem with my project only now
Again thank you for any advice.

[JohnTopley]

Try

=IF(OR(B3="",B3="XX",CODE(LEFT(B3))=111),"",MAX(A$1:A2)+1)

[AliGW]

The FIND function is case sensitive. Try this:

=IF(OR(B2="";B2="XX");"";IF(ISERROR(FIND("o";LEFT(B2)));MAX(A$1:A1)+1;""))

[Cubajz]

The FIND function is case sensitive. Try this:

=IF(OR(B2="";B2="XX");"";IF(ISERROR(FIND("o";LEFT(B2)));MAX(A$1:A1)+1;""))

Works! Thanks! Solved

[AliGW]

You're welcome!