Custom Section Numbering

M

MAnderson

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.
 
B

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
 
R

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
 
M

MAnderson

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.
 
G

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
 
M

Max

MAnderson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top