Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Auto numbering (2003)

    Lounger,

    I need your help once again.
    I would like to build a formula that would create cell numbering based on two values.

    The attached spreadsheet describes what I'm after

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Auto numbering (2003)

    Hi Dean,

    This may be what you are looking for - check the updated spreadsheet!

    Good Luck!

    Peter Moran

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto numbering (2003)

    Neat formula Peter. Very nicely worked out!
    Regards,
    Rudi

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Auto numbering (2003)

    Peter,

    Thanks for the quick response. Your suggestion works fine, however there seem to be a problem in that if there are more than 9 sub numbers it recalculates the main number, ie 2.9 next number becomes 3 where it may need to be 2.10 etc

    I hope that makes sense, any thoughts?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Auto numbering (2003)

    The formula as proposed doesn't work at all on systems that do not use a point as decimal separator. Try this formula in B7, and fill down as far as needed:

    =IF(C7<>"",COUNTA(C$7:C7)&"."&"1",LEFT(B6,FIND("." ,B6)-1)&"."&MID(B6,FIND(".",B6)+1,10)+1)

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto numbering (2003)

    Hans. Can I compliment you again...and again and again.... I never cease to be surprised at how you string together a couple of functions, and turn it into a work of art to do exactly what is required.
    3 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> and a <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> to you <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26>
    Regards,
    Rudi

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Auto numbering (2003)

    Hans,

    That is exactly what I need - Thanks again

    Could you give me a brief summary on how formula works?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Auto numbering (2003)

    The underlying idea is the same as that provided by petermoran. Look (for example) at the formula in row 10

    =IF(C10<>"",COUNTA(C$7:C10)&"."&"1",LEFT(B9,FIND(" .",B9)-1)&"."&MID(B9,FIND(".",B9)+1,10)+1)

    If C10 is not blank, you want to start a new level. COUNTA(C$7:C10) is the number of non-blank cells in the range in column C from row 7 to the current row 10, i.e. the current heading level. This is concatenated with .1

    If C10 is blank, you want to continue the current level with the next sequence number. We look at the cell above the current one, i.e. B9.
    FIND(".",B9) returns the position of the period in B9. For example, if B9 is 3.5, the period is in position 2 and if B9 is 12.4, the period is in position 3.
    LEFT(B9,FIND(".",B9)-1) is the part of B9 before the period. This is used without modification.
    MID(B9,FIND(".",B9)+1,10) is the part of B9 after the period (the 10 is an arbitrary length greater than the maximum number of decimals); this is incremented by 1 to get the next sequence number.
    These are concatenated with a period in between.

    BTW, the formula in B7 can be simplified to

    =IF(C7<>"",COUNTA(C$7:C7)&".1",LEFT(B6,FIND(".",B6 ))&MID(B6,FIND(".",B6)+1,10)+1)

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Auto numbering (2003)

    Hans,

    You

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •