• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.

Thanks again for your time.
 
Last edited:

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. :p
 

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:

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. :cool:
 
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.
 
Top