# Creating a formula for Chapter series numbering I can drag

#### Jimmy QU

##### New Member
Hi All,

I just had this issue I cannot find the best way to do it and you might already have the solution. Basically I need a formula that can do this.
5
5.1
5.2
5.3
....
5.9
5.10
5.11
5.12
...
5.99
5.100
...
5.xxxxxxxx
After I type 5 and 5.1 it would be able to drag all the way as the list goes. (Optional function) When I delete one of them, the series number below will be automatically adjust the numbers, no matter if i move up the cell below or not.
Basically it's a word function in excel, the number shows the level of titles.
Could someone help me please? Thank you.

I have attempted but failed, the challenge is that it's not a number increase as the next one after 5.9 becomes 6. Also I could not do a combination of joining a formula with numbering and text start at 5 at the same time. And I don't have a space to put a numbering bar in the spreadsheet just to combine the text. Also as you can see it's not a number, as 5.10 will be displayed as 5.1.

Last edited:

#### AlanSidman

##### Active Member
Change 5.1 to 5.01 and then drag down. See if this does what you want.

#### Jimmy QU

##### New Member
Change 5.1 to 5.01 and then drag down. See if this does what you want.
Thanks Alan, that's a smart shortcut. really appreciate that just haven't thought about it.

But if anyone still can work on this problem I will be still curious to know, as I am basically working on Excel as a word and all those prefilled forms has numbering like book chapters and sections, it will go like, 5, 5.1, 5.2.10, 5.5.15.6.....Sounds a bit crazy.

#### pecoflyer

##### Active Member
Wouldn't MSWord be better suited for your job?

#### vletm

##### Excel Ninja
Jimmy QU
You could test this ...
Note the 1st 'number' should have to be like 1.0 ( not only 1 )!

#### Attachments

• 31 KB Views: 6

#### Jimmy QU

##### New Member
Wouldn't MSWord be better suited for your job?
Yeah it could be. As my work is calculating all the costs and giving explanation and applied codes, I can do in 2 steps.

#### Jimmy QU

##### New Member
Jimmy QU
You could test this ...
Note the 1st 'number' should have to be like 1.0 ( not only 1 )!
thanks man, I will try tomorrow at work. I am looking forward to see it!

#### Haz

##### Active Member
Type 5 in A1, then in A2:
=LOOKUP(999, \$A\$1:A1) & "." & COUNTIF(\$A\$1:A1, LOOKUP(999, \$A\$1:A1) & "*") + 1

#### Jimmy QU

##### New Member
Jimmy QU
You could test this ...
Note the 1st 'number' should have to be like 1.0 ( not only 1 )!
Hey Vletm, thanks for your contribution. Really appreciate. It might be some small bugs there stopping it doing number starting with 2 or 3....I found an alternative solution below from Haz, and thanks again.

#### Jimmy QU

##### New Member
Type 5 in A1, then in A2:
=LOOKUP(999, \$A\$1:A1) & "." & COUNTIF(\$A\$1:A1, LOOKUP(999, \$A\$1:A1) & "*") + 1
Your solution is the best so far. It not only shows 5, 5.1, 5.2.. also does 5.1, 5.1.1, 5.1.2,....and when I add a row between, I only need to do drag another 2 cells to make all the changes. Once the formula is working, it can copy all over the place in the same row as long as it's in A row. What a magic!!! Thank you!!!!!!

Last edited:

#### bosco_yip

##### Excel Ninja
Or…….

A1 enter 5.1

then in A2, formula copied down :

=A\$1& "." &ROW(A1)

Regards
Bosco

#### Jimmy QU

##### New Member
Hey Bosco, Thanks for your input. This is another great idea! Sometimes if the spreadsheet does not start from Row 1, if the cell is A20, the formula becomes =A\$20&"."&ROW(A1). when a row is added, the formula need to drag down to the end. Excellent simple solution, thanks Bosco.

Last edited by a moderator:

#### vletm

##### Excel Ninja
Jimmy QU - It might be some small bugs there stopping it doing number starting with 2 or 3.
Yes - if You skipped the 2nd line
Note the 1st 'number' should have to be like 1.0 ( not only 1 )!
... alternatives are always okay!

#### Jimmy QU

##### New Member
Announcement: THIS PROBLEM HAS BEEN SOLVED. Thanks everyone who have spent time on my problem. But if anyone like to participate feel free to put your solutions here. There are many ways for doing things, and certainly, each journey to find the answer is more beautiful than the answer itself. You are all beautiful and worthy.