1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by Jimmy QU, Aug 20, 2018.

  1. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    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: Aug 20, 2018
  2. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    Change 5.1 to 5.01 and then drag down. See if this does what you want.
    Jimmy QU likes this.
  3. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    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
  4. pecoflyer

    pecoflyer Active Member

    Messages:
    258
    Wouldn't MSWord be better suited for your job?
    Jimmy QU likes this.
  5. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Jimmy QU
    You could test this ...
    Note the 1st 'number' should have to be like 1.0 ( not only 1 )!

    Attached Files:

    Jimmy QU likes this.
  6. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    Yeah it could be. As my work is calculating all the costs and giving explanation and applied codes, I can do in 2 steps.
  7. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    thanks man, I will try tomorrow at work. I am looking forward to see it!
  8. Haz

    Haz Active Member

    Messages:
    114
    Type 5 in A1, then in A2:
    =LOOKUP(999, $A$1:A1) & "." & COUNTIF($A$1:A1, LOOKUP(999, $A$1:A1) & "*") + 1
    Jimmy QU likes this.
  9. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    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.
  10. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    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: Aug 21, 2018
  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,005
    Or…….

    A1 enter 5.1

    then in A2, formula copied down :

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

    Regards
    Bosco
  12. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    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: Aug 21, 2018
  13. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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 likes this.
  14. Jimmy QU

    Jimmy QU New Member

    Messages:
    9
    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.

Share This Page