Sequential Numbering 1 1.1 1.2 2 2.1....

[ColinGu]

Dear all,
I would like to use sequential numbering for the below Project plan.
I would like to number
- the Projects with 1, 2, 3, 4
- the Milestones with 1.1., 1.2, 1.3, ... 2.1, 2.2, 2.3....
What Excel formula can be used?
Thank you very much for your help

Column A____Column B___Column C
(Numbering)_(Projects)___(Milestones)

1 __________Project A
1.1____________________Milestone a
1.2 ___________________Milestone b
1.3____________________Milestone c
...
2__________Project B
2.1___________________Milestone x
2.2___________________Milestone y
2.3 ....
2.4
...
3
3.1
...

 

[Eric W]

Here's one version:

=IF(ISTEXT(B2),COUNTA($B$1:B2),IF(ISTEXT(C2),A1+0.1,""))

Put it in A2 and copy it down. If your current Project A is on row 1, insert a blank row above it. This should work if: You only have project names in Column B, you only have milestones in Column B, there are no blank rows in a project (although you can have blank lines between projects), and you don't have more than 9 milestones per project. All of these can be handled, but the formulas get more complicated, and I don't know which ones apply.

Good luck!

 

[nswaper]

Dear all,
I would like to use sequential numbering for the below Project plan.
I would like to number
- the Projects with 1, 2, 3, 4
- the Milestones with 1.1., 1.2, 1.3, ... 2.1, 2.2, 2.3....
What Excel formula can be used?
Thank you very much for your help

Column A____Column B___Column C
(Numbering)_(Projects)___(Milestones)

1 __________Project A
1.1____________________Milestone a
1.2 ___________________Milestone b
1.3____________________Milestone c
...
2__________Project B
2.1___________________Milestone x
2.2___________________Milestone y
2.3 ....
2.4
...
3
3.1
...

Here is the code and formula:

You need to paste this Formula in G1 cell.
You can have values up to Cell A1, B1, C1, D1, E1 and F1 (i.e 6 values) so that you can paste the formula in G1 to cover all Values from A1 to F1.
After that, you can just Drag Down the G1 cell to autofill the formula in below rows.

=IF(SUMPRODUCT(--($B1:$F1<>""))=0,LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1),IF(SUMPRODUCT(--($C1:$F1<>""))=0,LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1)&"."&LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1),IF(SUMPRODUCT(--($D1:$F1<>""))=0,LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1)&"."&LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)&"."&LOOKUP(2,1/($C$1:$C1<>""),$C$1:$C1),IF(SUMPRODUCT(--($E1:$F1<>""))=0,LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1)&"."&LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)&"."&LOOKUP(2,1/($C$1:$C1<>""),$C$1:$C1)&"."&LOOKUP(2,1/($D$1:$D1<>""),$D$1:$D1),IF(SUMPRODUCT(--($F1:$F1<>""))=0,LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1)&"."&LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)&"."&LOOKUP(2,1/($C$1:$C1<>""),$C$1:$C1)&"."&LOOKUP(2,1/($D$1:$D1<>""),$D$1:$D1)&"."&LOOKUP(2,1/($E$1:$E1<>""),$E$1:$E1),LOOKUP(2,1/($A$1:$A1<>""),$A$1:$A1)&"."&LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)&"."&LOOKUP(2,1/($C$1:$C1<>""),$C$1:$C1)&"."&LOOKUP(2,1/($D$1:$D1<>""),$D$1:$D1)&"."&LOOKUP(2,1/($E$1:$E1<>""),$E$1:$E1)&"."&LOOKUP(2,1/($F$1:$F1<>""),$F$1:$F1))))))

 

[Rick Rothstein]

Assuming the "Project A" is located in cell B1, then put a 1 in cell A1 and then put this formula in cell A2 and copy it down to the end of your data...

=IF(B2="","",IF(B1="",1+INT(MAX(A$1:A1)),A1+0.1))

If, on the other hand, Row 1 contains header text and Project A is located in cell B2, then put this formula in cell A2 and copy it down to the end of your data...

=IF(B2="","",IF(OR(ROW()=2,B1=""),1+INT(MAX(A$1:A1)),A1+0.1))

 

[Joe4]

Not sure why this post is getting new attention. The question was asked and answered over three years ago!

 

[Rick Rothstein]

Not sure why this post is getting new attention. The question was asked and answered over three years ago!

Just being thorough I guess.