I have a worksheet where i want to number a column sequentially but not every
row. I put number 1 in column A1 and then put the formula A1+1 and copy it
down, sometimes i have blank rows where i don't need any numbers, sometimes i
delete and add rows. Is there a way without just copy and paste to achieve
the same results?
--
Scott Hemphill

Hi Scott,

Try this
Put 1 in A1
Click on the fill handle and drag down to row 20
Hold down Ctrl and let go the fill handle then let
go the left button.
(To find the fill handle hover your cursor over the bottom right
of the active cell until it turns into a little cross)

Play with a few variations. Put 1 in A1 and 2 in B1
highlight both cells then click on the fill handle and drag
down to row 20. This accomplishes the same thing as before
without the Ctrl button.however if you use the Ctrl
button you will get repeating series of 1 and 2 running
down the column

Now try 1 in A1, then highlight A1 to A4, then grab the fill handle
and drag down, once again see the difference with and without
the fill handle.

Keep playing and you will find all sorts of handy shortcuts.

HTH
Martin

"Scott Hemphill" wrote in message
...
I have a worksheet where i want to number a column sequentially but not
every
row. I put number 1 in column A1 and then put the formula A1+1 and copy
it
down, sometimes i have blank rows where i don't need any numbers,
sometimes i
delete and add rows. Is there a way without just copy and paste to achieve
the same results?
--
Scott Hemphill

so when i delete or add a row to this formula, is there a way to keep it
numbering sequentially?
--
Scott Hemphill


"MartinW" wrote:

Hi Scott,

Try this
Put 1 in A1
Click on the fill handle and drag down to row 20
Hold down Ctrl and let go the fill handle then let
go the left button.
(To find the fill handle hover your cursor over the bottom right
of the active cell until it turns into a little cross)

Play with a few variations. Put 1 in A1 and 2 in B1
highlight both cells then click on the fill handle and drag
down to row 20. This accomplishes the same thing as before
without the Ctrl button.however if you use the Ctrl
button you will get repeating series of 1 and 2 running
down the column

Now try 1 in A1, then highlight A1 to A4, then grab the fill handle
and drag down, once again see the difference with and without
the fill handle.

Keep playing and you will find all sorts of handy shortcuts.

HTH
Martin

"Scott Hemphill" wrote in message
...
I have a worksheet where i want to number a column sequentially but not
every
row. I put number 1 in column A1 and then put the formula A1+1 and copy
it
down, sometimes i have blank rows where i don't need any numbers,
sometimes i
delete and add rows. Is there a way without just copy and paste to achieve
the same results?
--
Scott Hemphill

Hi Scott,

=ROW()
This formula in A1 and dragged down will allow you to add and delete
rows without disturbing the sequential numbering but it will not allow for
intermittent blank rows.

This may be closer to what you are looking for.
Put 1 in A1 and 2 in A2.
Put this in A3 =COUNT($A$1:A2)+1
Drag down as far as is needed

This will allow you to delete or add entire rows and will also allow you to
delete or reinstate individual cells and the other cells should adjust
accordingly.

Is that closer to what you are looking for?

HTH
Martin


"Scott Hemphill" wrote in message
...
so when i delete or add a row to this formula, is there a way to keep it
numbering sequentially?
--
Scott Hemphill


"MartinW" wrote:

Hi Scott,

Try this
Put 1 in A1
Click on the fill handle and drag down to row 20
Hold down Ctrl and let go the fill handle then let
go the left button.
(To find the fill handle hover your cursor over the bottom right
of the active cell until it turns into a little cross)

Play with a few variations. Put 1 in A1 and 2 in B1
highlight both cells then click on the fill handle and drag
down to row 20. This accomplishes the same thing as before
without the Ctrl button.however if you use the Ctrl
button you will get repeating series of 1 and 2 running
down the column

Now try 1 in A1, then highlight A1 to A4, then grab the fill handle
and drag down, once again see the difference with and without
the fill handle.

Keep playing and you will find all sorts of handy shortcuts.

HTH
Martin

"Scott Hemphill" wrote in
message
...
I have a worksheet where i want to number a column sequentially but not
every
row. I put number 1 in column A1 and then put the formula A1+1 and
copy
it
down, sometimes i have blank rows where i don't need any numbers,
sometimes i
delete and add rows. Is there a way without just copy and paste to
achieve
the same results?
--
Scott Hemphill

Woops, not quite. It doesn't allow you to reinstate numbers.

HTH
Martin


"MartinW" wrote in message
...
Hi Scott,

=ROW()
This formula in A1 and dragged down will allow you to add and delete
rows without disturbing the sequential numbering but it will not allow for
intermittent blank rows.

This may be closer to what you are looking for.
Put 1 in A1 and 2 in A2.
Put this in A3 =COUNT($A$1:A2)+1
Drag down as far as is needed

This will allow you to delete or add entire rows and will also allow you
to
delete or reinstate individual cells and the other cells should adjust
accordingly.

Is that closer to what you are looking for?

HTH
Martin


"Scott Hemphill" wrote in
message ...
so when i delete or add a row to this formula, is there a way to keep it
numbering sequentially?
--
Scott Hemphill


"MartinW" wrote:

Hi Scott,

Try this
Put 1 in A1
Click on the fill handle and drag down to row 20
Hold down Ctrl and let go the fill handle then let
go the left button.
(To find the fill handle hover your cursor over the bottom right
of the active cell until it turns into a little cross)

Play with a few variations. Put 1 in A1 and 2 in B1
highlight both cells then click on the fill handle and drag
down to row 20. This accomplishes the same thing as before
without the Ctrl button.however if you use the Ctrl
button you will get repeating series of 1 and 2 running
down the column

Now try 1 in A1, then highlight A1 to A4, then grab the fill handle
and drag down, once again see the difference with and without
the fill handle.

Keep playing and you will find all sorts of handy shortcuts.

HTH
Martin

"Scott Hemphill" wrote in
message
...
I have a worksheet where i want to number a column sequentially but not
every
row. I put number 1 in column A1 and then put the formula A1+1 and
copy
it
down, sometimes i have blank rows where i don't need any numbers,
sometimes i
delete and add rows. Is there a way without just copy and paste to
achieve
the same results?
--
Scott Hemphill

Playing around with that approach, it appears to be a
little bit fragile, so you may want to apply conditional formatting
to the entire column with;
Formula is: =COUNTIF(A:A,A1)1
This should highlight the problem if it falls down.

HTH
Martin

"MartinW" wrote in message
...
Woops, not quite. It doesn't allow you to reinstate numbers.

HTH
Martin


"MartinW" wrote in message
...
Hi Scott,

=ROW()
This formula in A1 and dragged down will allow you to add and delete
rows without disturbing the sequential numbering but it will not allow
for
intermittent blank rows.

This may be closer to what you are looking for.
Put 1 in A1 and 2 in A2.
Put this in A3 =COUNT($A$1:A2)+1
Drag down as far as is needed

This will allow you to delete or add entire rows and will also allow you
to
delete or reinstate individual cells and the other cells should adjust
accordingly.

Is that closer to what you are looking for?

HTH
Martin


"Scott Hemphill" wrote in
message ...
so when i delete or add a row to this formula, is there a way to keep it
numbering sequentially?
--
Scott Hemphill


"MartinW" wrote:

Hi Scott,

Try this
Put 1 in A1
Click on the fill handle and drag down to row 20
Hold down Ctrl and let go the fill handle then let
go the left button.
(To find the fill handle hover your cursor over the bottom right
of the active cell until it turns into a little cross)

Play with a few variations. Put 1 in A1 and 2 in B1
highlight both cells then click on the fill handle and drag
down to row 20. This accomplishes the same thing as before
without the Ctrl button.however if you use the Ctrl
button you will get repeating series of 1 and 2 running
down the column

Now try 1 in A1, then highlight A1 to A4, then grab the fill handle
and drag down, once again see the difference with and without
the fill handle.

Keep playing and you will find all sorts of handy shortcuts.

HTH
Martin

"Scott Hemphill" wrote in
message
...
I have a worksheet where i want to number a column sequentially but
not
every
row. I put number 1 in column A1 and then put the formula A1+1 and
copy
it
down, sometimes i have blank rows where i don't need any numbers,
sometimes i
delete and add rows. Is there a way without just copy and paste to
achieve
the same results?
--
Scott Hemphill