Auto numbering cells

[Guest]

I have an excel 2000 sheet that has a number of rows that cut across column A
that are in effect group headers. Each of these headings is a whole number,
starting with the number 1.
e.g. A1 = 1
A5 = 2
A11 = 3 etc.

The cells in between the groups headers start with the previous cells whole
number and are incremented by .1
i.e.
A1 1
A2 1.1
A3 1.2
A4 1.3
A5 2.
A6 2.1
A7 2.2
A8 2.3
A9 2.4
A10 2.5
A11 3
A12 3.1

In the example above, if I insert rows into group 1, I have to manually
renumber the remaining cells in the group. E.g. if I insert two rows after
A1, I have to manually renumber cells A2 to the new A6, 1.1, 1.2 etc. This
may not seem like an issue but sometimes I need to create a new 1.1 when I
have 50 or so in the group that need to be renumbered.

Is there any way that I could select, for example, cells A2 to A6 and have
them renumbered automatically 1.1 through 1.5. The range and the starting
whole number need to be variable.

Odd request I know but any help greatfully received.

 

[Niek Otten]

One way:

Can be done in more compact way, but this is easier to follow.
Use some helper columns. I start in column B.
B1: 1
B5: 2
B11:3

C1:1
C2: =IF(B2=0,C1,B2)

D1: 0
D2: =IF(B2=0,D1+1,0)

E1: =C1&IF(D1=0,"","."&D1)

Copy C2, D2 and E1 down as far as you need.

Column C contains your number/subnumber. Hide any columns you don't want to
see.

If you insert rows, just copy columns C,D and E in the new rows


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel