1. ## 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. ## Re: Auto numbering (2003)

Hi Dean,

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

Good Luck!

Peter Moran

3. ## Re: Auto numbering (2003)

Neat formula Peter. Very nicely worked out!

4. ## 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. ## 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. ## 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>

7. ## 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. ## 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. ## 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
•