How do i Autonumber the first column by avoiding the blank space and numbered cells - AM NEW TO BXCEL. Thanks

[Titus sabu]

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]SNO[/TD]
[TD]NAME[/TD]
[TD]CONTACT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PUBLIC SCHOOL 1[/TD]
[TD]9855666[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]930000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PUB SCHOOL 2[/TD]
[TD]82899633[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[vbanoob0807]

Hi Titus,

assuming your first col starts from Col A, and what i understand your numbered cells to avoid are like "930000", and you want to number off from Row 2 onwards, try input the formula below in cell A2 and drag down.

=IF(AND(NOT(ISBLANK(B2)),NOT(ISNUMBER(B2))),ROWS($B$2:$B2)-COUNTBLANK($B$2:$B2)-COUNT($B$2:$B2),"")

 

[njimack]

=if(istext(b2),max(a$1:a1)+1,"")

 

[vbanoob0807]

=if(istext(b2),max(a$1:a1)+1,"")

Fabulous

 

[Titus sabu]

Hi Titus,

assuming your first col starts from Col A, and what i understand your numbered cells to avoid are like "930000", and you want to number off from Row 2 onwards, try input the formula below in cell A2 and drag down.

=IF(AND(NOT(ISBLANK(B2)),NOT(ISNUMBER(B2))),ROWS($B$2:$B2)-COUNTBLANK($B$2:$B2)-COUNT($B$2:$B2),"")

vbanoob0807, Thank you So Much. Exactly what i needed.

 

[Titus sabu]

Thank you Neil, so much for This reply. It did what exactly i need. Since I am new to excel this really blew me off since it had taken hours manually.

 

[Titus sabu]

Thank you !!