Deciphering Formula!

[dannyok90]

Hi All,

please see the formula below.

I obtained this formula some time ago and always use it to number cells.


Id like to decipher it so instead of being 1.1, 1.2, 2.0, 2.1 etc.


When I set the top level number as 000 it numbers 001, 002 or when the top level is 100 its 101, 102 etc


Any help would be much appreciated


=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A9,-1,0,1,1),"",""))),"01",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))),OFFSET(A9,-1,0,1,1)&".1",LEFT(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1)))&IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))),VALUE(RIGHT(OFFSET(A9,-1,0,1,1),LEN(OFFSET(A9,-1,0,1,1))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))))+1,VALUE(MID(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))+1,(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",2))-FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))-1)))+1)))

 

[Eric W]

Consider this formula:

	A
1.5

<tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1.1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1.2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1.3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1.4[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1.6[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1.7[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2.1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2.2[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2.3[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2.4[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2.6[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2.7[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]2.8[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]2.9[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]2.10[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]2.11[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]2.12[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]3.1[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]=LEFT(A3,FIND(".",A3&".")-1)&"."&IFERROR(MID(A3,FIND(".",A3)+1,9)+1,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Put your starting value in A3. Put the formula in A4 and drag down as far as needed. If you want to start a new range, just put a whole number in and the formulas below it will adjust. I did that with rows 11 and 24.

 

[Scott Huish]

If you just want whole numbers, change the formula to =A8+1
Copy down.