Sequential Numbers

[Guest]

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?

 

[MartinW]

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

 

[Guest]

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

 

[MartinW]

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

 

[MartinW]

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

HTH
Martin

 

[MartinW]

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

 

[Guest]

Martin,
thanks for your help this has helped me get closer than i ever have, can you
tell me how to apply the conditional formatting? Will this help when i add a
row?

 

[MartinW]

Hi Scott,

To apply the CF
Highlight A1 to say A1000 or however far you are likely to need.
Go to Format>Conditional Formatting
Select Formula is from the pulldown menu
Put this formula in the next box =COUNTIF(A:A,A1)>1
Click on the Format button
Click on the Patterns Tab and select a fill color
Then OK and OK again.

This won't help when adding or deleting rows or deleting cells.
All it will do is highlight duplicate numbers in the column.
So if you happen to get a bit cavalier about deleting this and
adding that etc. it will show up as a sort of alarm that you have
pushed things a little too far <g>

I think all this should work. If it is still not what you need
please explain your actual requirements in more detail

HTH
Martin

 

[Guest]

Martin,
Thanks for your help
--
Scott Hemphill

Hi Scott,

To apply the CF
Highlight A1 to say A1000 or however far you are likely to need.
Go to Format>Conditional Formatting
Select Formula is from the pulldown menu
Put this formula in the next box =COUNTIF(A:A,A1)>1
Click on the Format button
Click on the Patterns Tab and select a fill color
Then OK and OK again.

This won't help when adding or deleting rows or deleting cells.
All it will do is highlight duplicate numbers in the column.
So if you happen to get a bit cavalier about deleting this and
adding that etc. it will show up as a sort of alarm that you have
pushed things a little too far <g>

I think all this should work. If it is still not what you need
please explain your actual requirements in more detail

HTH
Martin