Excel Number in a Column
 

I'm trying to create a numbering system in a spread sheet with sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.

Excel Number in a Column
 

Do you mean

in A1 type formula and copy that down...
="2." & ROW()

OR

A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 and then 3, 3.1




If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

I'm trying to create a numbering system in a spread sheet with sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.

Excel Number in a Column
 

Hi Jacob, yes and no, for example:

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would
like and also be able to add then 3.0, 3.1, etc.

I would like it to be able to auto file as well if that is possible.

I can do it with out the decimal as a whole number but when i do the
decimal, it increments to the following:
2.1
3.1
4.1
5.1
etc.



"Jacob Skaria" wrote:

Do you mean

in A1 type formula and copy that down...
="2." & ROW()

OR

A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 and then 3, 3.1




If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

I'm trying to create a numbering system in a spread sheet with sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.

Excel Number in a Column
 

btw, i can get it to do the following:
A1= 2.1
A2= 2.2
A3= 2.3
A4= 2.4
A5= 2.5
A6= 2.6
A7= 2.7
A8= 2.8
A9= 2.9
A10= 3.0
A11= 3.1

I would like to continue with
A10= 2.10
A11= 2.11
A12= 2.12
etc, to what ever range i want before changing to
A199= 3.0 or higher.

"Jim S" wrote:

Hi Jacob, yes and no, for example:

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would
like and also be able to add then 3.0, 3.1, etc.

I would like it to be able to auto file as well if that is possible.

I can do it with out the decimal as a whole number but when i do the
decimal, it increments to the following:
2.1
3.1
4.1
5.1
etc.



"Jacob Skaria" wrote:

Do you mean

in A1 type formula and copy that down...
="2." & ROW()

OR

A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 and then 3, 3.1




If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

I'm trying to create a numbering system in a spread sheet with sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.

Excel Number in a Column
 

If a macro solution would be acceptable, here is one that should work nicely
for you. Simply put the starting number in a cell, then select from that
cell to the last cell you want to put your serial number in, and finally run
the macro.

Sub FillDownWithDecimals()
Dim X As Long, W As String, F As String, S As String
S = Selection(1).Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
For X = 0 To Selection.Count - 1
With Selection(1).Offset(X)
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = W & "." & F
End With
F = CStr(Val(F + 1))
Next
End Sub

Note: The macro changes the format of the selected cells to text (in order
to preserve trailing zeroes) and then right-aligns the text within the cell.

--
Rick (MVP - Excel)



"Jim S" wrote in message
...
btw, i can get it to do the following:
A1= 2.1
A2= 2.2
A3= 2.3
A4= 2.4
A5= 2.5
A6= 2.6
A7= 2.7
A8= 2.8
A9= 2.9
A10= 3.0
A11= 3.1

I would like to continue with
A10= 2.10
A11= 2.11
A12= 2.12
etc, to what ever range i want before changing to
A199= 3.0 or higher.

"Jim S" wrote:

Hi Jacob, yes and no, for example:

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i
would
like and also be able to add then 3.0, 3.1, etc.

I would like it to be able to auto file as well if that is possible.

I can do it with out the decimal as a whole number but when i do the
decimal, it increments to the following:
2.1
3.1
4.1
5.1
etc.



"Jacob Skaria" wrote:

Do you mean

in A1 type formula and copy that down...
="2." & ROW()

OR

A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 and then 3, 3.1




If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

I'm trying to create a numbering system in a spread sheet with
sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.

Excel Number in a Column
 

Jacob, your formula of ="2." & ROW() works if i'm in row A1 but if i have a
header row and want to start in A2= 2.0 or 2.1 what do I need to use?


"Jim S" wrote:

btw, i can get it to do the following:
A1= 2.1
A2= 2.2
A3= 2.3
A4= 2.4
A5= 2.5
A6= 2.6
A7= 2.7
A8= 2.8
A9= 2.9
A10= 3.0
A11= 3.1

I would like to continue with
A10= 2.10
A11= 2.11
A12= 2.12
etc, to what ever range i want before changing to
A199= 3.0 or higher.

"Jim S" wrote:

Hi Jacob, yes and no, for example:

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would
like and also be able to add then 3.0, 3.1, etc.

I would like it to be able to auto file as well if that is possible.

I can do it with out the decimal as a whole number but when i do the
decimal, it increments to the following:
2.1
3.1
4.1
5.1
etc.



"Jacob Skaria" wrote:

Do you mean

in A1 type formula and copy that down...
="2." & ROW()

OR

A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 and then 3, 3.1




If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

I'm trying to create a numbering system in a spread sheet with sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.

Excel Number in a Column
 

Here is the macro modified to let you change numbering within the
selection...

Sub FillDownWithDecimals()
Dim X As Long, W As String, F As String, S As String
S = Selection(1).Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
For X = 0 To Selection.Count - 1
With Selection(1).Offset(X)
If .Text < "" Then
S = .Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
End If
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = W & "." & F
End With
F = CStr(Val(F + 1))
Next
End Sub

To see what I mean, go to a blank column and in any cell in that column, put
2.0; then, in the same column, put 3.0 in a cell some number of rows below
the 2.0 cell; now, make a selection starting with the 2.0 cell and going
past the 3.0 cell... run the macro and the 2.# series will stop when it
reaches the 3.0 cell and from then on, the number will be 3.# until the end
of the selection. You can encompass as many new numbering starting points
(the 2.0 and 3.0 values, although you don't have to start with those number)
as you want and the code will adapt to them.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
If a macro solution would be acceptable, here is one that should work
nicely for you. Simply put the starting number in a cell, then select from
that cell to the last cell you want to put your serial number in, and
finally run the macro.

Sub FillDownWithDecimals()
Dim X As Long, W As String, F As String, S As String
S = Selection(1).Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
For X = 0 To Selection.Count - 1
With Selection(1).Offset(X)
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = W & "." & F
End With
F = CStr(Val(F + 1))
Next
End Sub

Note: The macro changes the format of the selected cells to text (in order
to preserve trailing zeroes) and then right-aligns the text within the
cell.

--
Rick (MVP - Excel)



"Jim S" wrote in message
...
btw, i can get it to do the following:
A1= 2.1
A2= 2.2
A3= 2.3
A4= 2.4
A5= 2.5
A6= 2.6
A7= 2.7
A8= 2.8
A9= 2.9
A10= 3.0
A11= 3.1

I would like to continue with
A10= 2.10
A11= 2.11
A12= 2.12
etc, to what ever range i want before changing to
A199= 3.0 or higher.

"Jim S" wrote:

Hi Jacob, yes and no, for example:

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i
would
like and also be able to add then 3.0, 3.1, etc.

I would like it to be able to auto file as well if that is possible.

I can do it with out the decimal as a whole number but when i do the
decimal, it increments to the following:
2.1
3.1
4.1
5.1
etc.



"Jacob Skaria" wrote:

Do you mean

in A1 type formula and copy that down...
="2." & ROW()

OR

A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 and then 3, 3.1




If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

I'm trying to create a numbering system in a spread sheet with
sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.

Excel Number in a Column
 

Hi Jim

In A2; try
="2." & (ROW()-1)

If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

Jacob, your formula of ="2." & ROW() works if i'm in row A1 but if i have a
header row and want to start in A2= 2.0 or 2.1 what do I need to use?


"Jim S" wrote:

btw, i can get it to do the following:
A1= 2.1
A2= 2.2
A3= 2.3
A4= 2.4
A5= 2.5
A6= 2.6
A7= 2.7
A8= 2.8
A9= 2.9
A10= 3.0
A11= 3.1

I would like to continue with
A10= 2.10
A11= 2.11
A12= 2.12
etc, to what ever range i want before changing to
A199= 3.0 or higher.

"Jim S" wrote:

Hi Jacob, yes and no, for example:

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would
like and also be able to add then 3.0, 3.1, etc.

I would like it to be able to auto file as well if that is possible.

I can do it with out the decimal as a whole number but when i do the
decimal, it increments to the following:
2.1
3.1
4.1
5.1
etc.



"Jacob Skaria" wrote:

Do you mean

in A1 type formula and copy that down...
="2." & ROW()

OR

A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 and then 3, 3.1




If this post helps click Yes
---------------
Jacob Skaria


"Jim S" wrote:

I'm trying to create a numbering system in a spread sheet with sequential
numbering using decimal points. i.e.

column A
Header
2.1
2.2
2.3
2.4
2.5

etc.