numbered list ?

[Dimitris254]

is it possible to have numbered list in Excel, for each row, like in Word?

I don't want a numbered list in a single cell, but this:

[row1] 1.1 text
[row2] 1.1.1 text
[row3] 1.1.2 text

which will be updated automatically depending on the indent level ?

 

[RickXL]

I can suggest a VBA solution.

The following code looks at column B and writes the ID, based on the indents in column B, into column A.

Sub IndentToID()
    Dim r    As Range
    Dim str  As String
    Dim pil  As Long
    Dim arr  As Variant
    
    str = "0"
    For Each r In Range("B2", Cells(Rows.Count, "B").End(xlUp))
        Select Case r.IndentLevel
            Case Is > pil
                str = str & Replace(String(r.IndentLevel - pil, "X"), "X", ".1")
            Case Is = pil
                str = Left(str, InStrRev(str, ".")) & Right(str, Len(str) - InStrRev(str, ".")) + 1
            Case Is < pil
                arr = Split(str, ".")
                ReDim Preserve arr(0 To r.IndentLevel)
                arr(UBound(arr)) = arr(UBound(arr)) + 1
                str = Join(arr, ".")
        End Select
        pil = r.IndentLevel
        With r.Offset(, -1)
            .Value = str
            .NumberFormat = "@"
        End With
    Next
End Sub

The code above needs to be pasted in to a new standard code Module and be executed every time you need the IDs updated.
You could link it to a Quick Access Toolbar button for that workbook.

 

[Dimitris254]

thanks for the time put into this, but it doesn't seem to work

for example, i put:
A1: "test 1"
A2: "test 1.1"
A3: "test 1.1.1"
with respective indents

but after running the macro A1 and A2 are simply replaced by 1 and 2

 

[RickXL]

I assumed that the data would have the ID (e.g. 1.2.3) would be written into column A and the indented strings would be in column B.

If that is not acceptable then you will need to say exactly what you are expecting.

Excel 2013
	A	B
1	ID	AA
2	1	asdf
3	1.1	sdfg
4	1.2	dfgj
5	1.3	fk
6	1.4	fk
7	1.5	fk
8	1.6	fk
9	1.7	hgf
10	1.8	sdfg
11	1.9	dfgj
12	1.10	fghk
13	1.11	glh,jl
14	1.12	gfhu
15	1.12.1	gfh
16	1.12.2	gfh
17	1.13	gfh
18	1.14	gfh
19	2	gfh
Sheet1

Thanks,

 

[Dimitris254]

I see the misunderstanding.

The way the sheet looks is simpler. What mentioned in the OP is (i tried to copied your columns, but it turned out very messed up ).

Can i send a sample sheet?

 

[RickXL]

You can't send me a spreadsheet but you could post one on line somewhere e.g. DropBox, SkyDrive etc.

However, a description should be enough.

I can't work out how many columns your data is in. 1 or 2?
Are you using the Indent Formatting option or are you prefixing some blanks or something else?

 

[shg]

There's a workbook with a pair of UDFs at https://app.box.com/s/hr2nl2mzirq2qxtwlkcw5dmjd6bcqqxv that will do this:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Level​

[/td][td="bgcolor:#F3F3F3"]

Ind​

[/td][td="bgcolor:#F3F3F3"]

WBS​

[/td][td="bgcolor:#F3F3F3"]

Description​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

0​

[/td][td="bgcolor:#E5E5E5"]

0 ​

[/td][td="bgcolor:#E5E5E5"]0[/td][td]USMC Combat Convoy Simulator[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]

1 ​

[/td][td="bgcolor:#E5E5E5"]**1[/td][td]**Non-Recurring Engineering[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****1.1[/td][td]****Hardware Design[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.1.1[/td][td]******AAR Station[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.1.1.1[/td][td]********Design[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.1.1.2[/td][td]********Docs[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.1.2[/td][td]******IOS[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.1.2.1[/td][td]********Design[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.1.2.2[/td][td]********Docs[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.1.3[/td][td]******Display[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.1.3.1[/td][td]********Projection Geometry & Projector Mount[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.1.3.2[/td][td]********Weapon spot tracking[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****1.2[/td][td]****Software Design[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.2.1[/td][td]******Simulation Host[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.1[/td][td]********Simulation framework[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.2[/td][td]********IG Integration[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.3[/td][td]********One SAF Integration[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.4[/td][td]********SE Core Integration[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.5[/td][td]********DIS/HLA (VR Link) Integration[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.6[/td][td]********CREW-2 Simulation[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.7[/td][td]********FBCB2 / JBC-P Integration[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.1.8[/td][td]********SINCGARS Simulation[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.2.2[/td][td]******IOS[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.2.1[/td][td]********Scenario authoring & control[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.2.2[/td][td]********Student monitoring & records[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.2.3[/td][td]********Learning management[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.2.4[/td][td]********Instructor Manual[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.2.3[/td][td]******AAR Station[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

31​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.2.4[/td][td]******MTVR & HMMWV Crew Stations[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.4.1[/td][td]********Dev Software (CM Labs)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

33​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.4.2[/td][td]********Model Development (CM Labs)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

34​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.4.3[/td][td]********User Manuals[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

35​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.2.5[/td][td]******Weapon Simulations[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

36​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.5.1[/td][td]********.50 HMG[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

37​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.5.2[/td][td]********M240G[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

38​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.5.3[/td][td]********M4[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

39​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.2.5.4[/td][td]********M16A4[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

40​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****1.3[/td][td]****Databases[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

41​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.3.1[/td][td]******Terrain[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

42​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.3.1.1[/td][td]********Imagery[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

43​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.3.1.2[/td][td]********DEM[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

44​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.3.2[/td][td]******Models[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

45​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****1.4[/td][td]****Data[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

46​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.4.1[/td][td]******ATP/TTPRR, including Cold-Start Procedure[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

47​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.1.1[/td][td]********PE[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

48​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.1.2[/td][td]********Tech[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

49​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.4.2[/td][td]******Information Assurance[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

50​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.4.3[/td][td]******ILS[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

51​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.3.1[/td][td]********LSA Plan[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

52​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.3.2[/td][td]********Provisioning Plan[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

53​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.3.3[/td][td]********Safety Plan[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

54​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.3.4[/td][td]********R&M Analysis[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

55​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.3.5[/td][td]********Life Cycle Cost Analysis[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

56​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********1.4.3.6[/td][td]********Planned Maintenance & Repair Manuals[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

57​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****1.5[/td][td]****Meetings and Reviews[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

58​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.5.1[/td][td]******PM[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

59​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.5.2[/td][td]******PE[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

60​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.5.3[/td][td]******HW[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

61​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.5.4[/td][td]******SW[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

62​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.5.5[/td][td]******DB[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

63​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****1.6[/td][td]****First Article Integration & Test[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

64​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.6.1[/td][td]******PM[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

65​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.6.2[/td][td]******PE[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

66​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.6.3[/td][td]******HW[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

67​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.6.4[/td][td]******SW[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

68​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******1.6.5[/td][td]******Tech[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

69​

[/td][td][/td][td="bgcolor:#E5E5E5"]

0 ​

[/td][td="bgcolor:#E5E5E5"][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

70​

[/td][td]

1​

[/td][td="bgcolor:#E5E5E5"]

1 ​

[/td][td="bgcolor:#E5E5E5"]**2[/td][td]**Recurring[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

71​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****2.1[/td][td]****Hardware & Software[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

72​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******2.1.1[/td][td]******Host Computer System[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

73​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******2.1.2[/td][td]******VR Link License[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

74​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******2.1.3[/td][td]******IOS[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

75​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.3.1[/td][td]********Computer System[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

76​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.3.2[/td][td]********FBCB2 / JBC-P (GFI)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

77​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.3.3[/td][td]********One SAF (GFI)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

78​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.3.4[/td][td]********Stealth IG (IDX-80, 3 OTW)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

79​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

5 ​

[/td][td="bgcolor:#E5E5E5"]**********2.1.3.4.1[/td][td]**********IDX-80, 3 channel[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

80​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

5 ​

[/td][td="bgcolor:#E5E5E5"]**********2.1.3.4.2[/td][td]**********VBS IG Channel Licenses[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

81​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******2.1.4[/td][td]******AAR Station[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

82​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.4.1[/td][td]********Computer System[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

83​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.4.2[/td][td]********Aural Cue / Radio Replay[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

84​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.4.3[/td][td]********Stealth IG (IDX-80, 3 OTW)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

85​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

5 ​

[/td][td="bgcolor:#E5E5E5"]**********2.1.4.3.1[/td][td]**********IDX-80, 3 channel[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

86​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

5 ​

[/td][td="bgcolor:#E5E5E5"]**********2.1.4.3.2[/td][td]**********VBS IG Channel Licenses[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

87​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******2.1.5[/td][td]******Crew Stations (set)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

88​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.1[/td][td]********Computer (vehicle model)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

89​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.2[/td][td]********Vehicle Model SW License[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

90​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.3[/td][td]********Aural Cue / Radio System[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

91​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.4[/td][td]********MTVR chassis & I/O (GFI)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

92​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.5[/td][td]********HMMWV chassis & I/O (GFI)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

93​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.6[/td][td]********Untethered weapons & trackers (GFI)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

94​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.7[/td][td]********Crew Station IG[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

95​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

5 ​

[/td][td="bgcolor:#E5E5E5"]**********2.1.5.7.1[/td][td]**********IDX 8000 HW & 3PSW[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

96​

[/td][td]

5​

[/td][td="bgcolor:#E5E5E5"]

5 ​

[/td][td="bgcolor:#E5E5E5"]**********2.1.5.7.2[/td][td]**********VBS IG Channel Licenses[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

97​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.5.8[/td][td]********CCTV[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

98​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******2.1.6[/td][td]******Display[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

99​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.6.1[/td][td]********Screens (Drywall, use as is)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

100​

[/td][td]

4​

[/td][td="bgcolor:#E5E5E5"]

4 ​

[/td][td="bgcolor:#E5E5E5"]********2.1.6.2[/td][td]********Projectors[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

101​

[/td][td]

3​

[/td][td="bgcolor:#E5E5E5"]

3 ​

[/td][td="bgcolor:#E5E5E5"]******2.1.7[/td][td]******Weapon Spot Trackers[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

102​

[/td][td]

2​

[/td][td="bgcolor:#E5E5E5"]

2 ​

[/td][td="bgcolor:#E5E5E5"]****2.2[/td][td]****Installation, Cold Start & Acceptance Testing[/td][/tr]
[/table]

What appears as asterisks above are indents in the workbook.

 

[Dimitris254]

here's a screenshot: http://i.imgur.com/yGsMpE6.jpg

as you see there is no specific pattern, task have different number of subtasks, which in turn don't have a pre-determined number of subsub tasks.

The only pattern is that there is an empty row between each task. And the indents.

a VBA solution would be something like (dummy code):

With selected cell
check indents until you find empty row
for no indent level > start numbering 1.,2.,3. etc.
for 1 indent level, check numbering of task and start 1.1, 1.2 etc.
for 2 indent levels, check numbering of subtask and start 1.1.1, 1.1.2 etc.

one important thing is that if there is text already in the cell, the numbering should not overwrite it but go infront of the pre-existing text.

PS: i take it that Excel has no automated function for lists like Word

[edit] @shg: this is exactly what i'm talking about, except that i don't have as you see in the image, i don't have separate A, B, C, columns.
But maybe it is a good idea to separate the task numbering from the actual name.

 

[shg]

[edit] @shg: this is exactly what i'm talking about, except that i don't have as you see in the image, i don't have separate A, B, C, columns.

But maybe it is a good idea to separate the task numbering from the actual name.

I do, because the task number is synthesized independent of the task description.

 

[RickXL]

Thanks shg for another take ...

I did not write my code with any blank rows in mind. So that will make a difference.
Also, when compared to shg's data, I assumed that indents would increase by one and not two per level.

However, if I use shg's data (with the indents halved) and add a change to my code that indents the WBS as well as the description I get a similar answer.

Excel 2013
	A	B
1	WBS	Description
2	1	USMC Combat Convoy Simulator
3	1.1	Non-Recurring Engineering
4	1.1.1	Hardware Design
5	1.1.1.1	AAR Station
6	1.1.1.1.1	Design
7	1.1.1.1.2	Docs
8	1.1.1.2	IOS
9	1.1.1.2.1	Design
10	1.1.1.2.2	Docs
11	1.1.1.3	Display
12	1.1.1.3.1	Projection Geometry & Projector Mount
13	1.1.1.3.2	Weapon spot tracking
14	1.1.2	Software Design
15	1.1.2.1	Simulation Host
16	1.1.2.1.1	Simulation framework
17	1.1.2.1.2	IG Integration
18	1.1.2.1.3	One SAF Integration
19	1.1.2.1.4	SE Core Integration
20	1.1.2.1.5	DIS/HLA (VR Link) Integration
21	1.1.2.1.6	CREW-2 Simulation
22	1.1.2.1.7	FBCB2 / JBC-P Integration
23	1.1.2.1.8	SINCGARS Simulation
24	1.1.2.2	IOS
25	1.1.2.2.1	Scenario authoring & control
26	1.1.2.2.2	Student monitoring & records
27	1.1.2.2.3	Learning management
28	1.1.2.2.4	Instructor Manual
29	1.1.2.3	AAR Station
30	1.1.2.4	MTVR & HMMWV Crew Stations
31	1.1.2.4.1	Dev Software (CM Labs)
32	1.1.2.4.2	Model Development (CM Labs)
33	1.1.2.4.3	User Manuals
34	1.1.2.5	Weapon Simulations
35	1.1.2.5.1	.50 HMG
36	1.1.2.5.2	M240G
37	1.1.2.5.3	M4
38	1.1.2.5.4	M16A4
39	1.1.3	Databases
40	1.1.3.1	Terrain
41	1.1.3.1.1	Imagery
42	1.1.3.1.2	DEM
43	1.1.3.2	Models
44	1.1.4	Data
45	1.1.4.1	ATP/TTPRR, including Cold-Start Procedure
46	1.1.4.1.1	PE
47	1.1.4.1.2	Tech
48	1.1.4.2	Information Assurance
49	1.1.4.3	ILS
50	1.1.4.3.1	LSA Plan
51	1.1.4.3.2	Provisioning Plan
52	1.1.4.3.3	Safety Plan
53	1.1.4.3.4	R&M Analysis
54	1.1.4.3.5	Life Cycle Cost Analysis
55	1.1.4.3.6	Planned Maintenance & Repair Manuals
56	1.1.5	Meetings and Reviews
57	1.1.5.1	PM
58	1.1.5.2	PE
59	1.1.5.3	HW
60	1.1.5.4	SW
61	1.1.5.5	DB
62	1.1.6	First Article Integration & Test
63	1.1.6.1	PM
64	1.1.6.2	PE
65	1.1.6.3	HW
66	1.1.6.4	SW
67	1.1.6.5	Tech
68	1.2	Recurring
69	1.2.1	Hardware & Software
70	1.2.1.1	Host Computer System
71	1.2.1.2	VR Link License
72	1.2.1.3	IOS
73	1.2.1.3.1	Computer System
74	1.2.1.3.2	FBCB2 / JBC-P (GFI)
75	1.2.1.3.3	One SAF (GFI)
76	1.2.1.3.4	Stealth IG (IDX-80, 3 OTW)
77	1.2.1.3.4.1	IDX-80, 3 channel
78	1.2.1.3.4.2	VBS IG Channel Licenses
79	1.2.1.4	AAR Station
80	1.2.1.4.1	Computer System
81	1.2.1.4.2	Aural Cue / Radio Replay
82	1.2.1.4.3	Stealth IG (IDX-80, 3 OTW)
83	1.2.1.4.3.1	IDX-80, 3 channel
84	1.2.1.4.3.2	VBS IG Channel Licenses
85	1.2.1.5	Crew Stations (set)
86	1.2.1.5.1	Computer (vehicle model)
87	1.2.1.5.2	Vehicle Model SW License
88	1.2.1.5.3	Aural Cue / Radio System
89	1.2.1.5.4	MTVR chassis & I/O (GFI)
90	1.2.1.5.5	HMMWV chassis & I/O (GFI)
91	1.2.1.5.6	Untethered weapons & trackers (GFI)
92	1.2.1.5.7	Crew Station IG
93	1.2.1.5.7.1	IDX 8000 HW & 3PSW
94	1.2.1.5.7.2	VBS IG Channel Licenses
95	1.2.1.5.8	CCTV
96	1.2.1.6	Display
97	1.2.1.6.1	Screens (Drywall, use as is)
98	1.2.1.6.2	Projectors
99	1.2.1.7	Weapon Spot Trackers
100	1.2.2	Installation, Cold Start & Acceptance Testing
Sheet2