Custom Section Numbering

[Guest]

Is it possible to specify a formula that creates a custom numbering format.
For example, I want to set up a column that can do this:

1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11, etc.

I tried to do decimal addition, but that doesn't allow 1.10, 1.11, 1.12, etc.

Is there a way to do this using the standard features of Excel, or does
someone know the formula to program this?

Thanks.

 

[Guest]

One way ..

Place this in the top cell (eg: in C5), then copy down:
="1."&ROWS($1:1)

 

[Bernie Deitrick]

=VALUE("1." & ROW())

Or

=VALUE("1." & ROW()-ROW($A$2) +1)

Change the $A$2 to the address of the first cell where this is entered.

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

Is it possible to specify a formula that creates a custom numbering

format.
For example, I want to set up a column that can do this:

1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11, etc.

I tried to do decimal addition, but that doesn't allow 1.10, 1.11, 1.12,
etc.

Is there a way to do this using the standard features of Excel, or does
someone know the formula to program this?

Could number the cells down the row with normal numbers (1,2,3,4,etc.) and
then custom format the column with this...

1\.#

and set the Horizontal Alignment to Left.

Rick

 

[Guest]

Thanks Max. This did what I wanted it to. The only shortcoming is that if
you add or remove a row in your spreadsheet it doesn't automatically
recalculate; I had to recopy the formula. Maybe you know a way to define the
formula to recalculate?

I thought maybe the other posts would do it, but I couldn't get any of the
other presented solutions to work.

Thanks again. If you have further input about the auto update let me know.

 

[Gord Dibben]

Enter 1.1 preceded by an apostrophe.

Right-click and drag down then release mouse button and "Fill Series"


Gord Dibben MS Excel MVP

 

[Guest]

Thanks Max. This did what I wanted it to. The only shortcoming is that if
you add or remove a row in your spreadsheet it doesn't automatically
recalculate; I had to recopy the formula. Maybe you know a way to define the
formula to recalculate?

If you delete a row in-between the filled range, it will recalc, ie the
resulting shortened range should return the correct sequence. If it doesn't
recalc auto, check and ensure that calc mode is set to auto (via
Tools>Options>Calculation tab). From your comments, believe your calc mode
might have been inadvertently set to manual.

If you insert new rows, you need to copy down the formula from the cell above.

I thought maybe the other posts would do it, but I couldn't get any of the
other presented solutions to work.

As the original poster, you should always try to post back to all responders
individually who offered you their thoughts. I do that if I'm the orig.
poster.

Anyway, except for Bernie's suggestions (where I think "1.10", "1.100", ..
would appear as "1.1" instead), I could get Rick's and Gord's suggestions to
work. But since Rick's/Gord's suggestions are not formulas, the numbering for
the range below would not adjust for any in-between row deletions, you would
need to re-fill the series from above.

---