auto-numbering help (during inserting rows)
 

Hello all:

I have the following formula in every cell in my "A" column:
=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"")

This serves the purpose so that whenever I type something in column B, it
will auto number the entries consecutively. When i delete a row, the
numbering still works and all the numbers are reorganized but still
consecutive.

However when i insert a row, the formula does not exist in the new cell in
column A.... and so my whole numbering gets thrown off.. Is there a way to
make it such that there is auto numbering when i insert rows? Perhaps by
having the formula above placed in the cell as soon as i insert a new row?
Or something that makes sure each row in column A has a similar formula that
allows this kind of auto-numbering?

Please, Any help would be appreciated,

Terrel Lobo

auto-numbering help (during inserting rows)
 

right click on the sheet tab and select view code. Paste in code like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set rng = Range(Cells(1, 1), Cells( _
Rows.Count, 2).End(xlUp)(1, 0))
rng.Formula = "=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"""")"
End Sub

This will probably affect your ability to paste on that worksheet. You
have to look at the trade off of convenience vice functionality.

--
Regards,
Tom Ogilvy


"LoboNetwork" wrote:

Hello all:

I have the following formula in every cell in my "A" column:
=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"")

This serves the purpose so that whenever I type something in column B, it
will auto number the entries consecutively. When i delete a row, the
numbering still works and all the numbers are reorganized but still
consecutive.

However when i insert a row, the formula does not exist in the new cell in
column A.... and so my whole numbering gets thrown off.. Is there a way to
make it such that there is auto numbering when i insert rows? Perhaps by
having the formula above placed in the cell as soon as i insert a new row?
Or something that makes sure each row in column A has a similar formula that
allows this kind of auto-numbering?

Please, Any help would be appreciated,

Terrel Lobo

auto-numbering help (during inserting rows)
 

Thanks Tom,
That is exactly what I wanted per my specifications... i found some
additional problems that came up in some existing spreadsheets but thats my
fault hehe.

"Tom Ogilvy" wrote:

right click on the sheet tab and select view code. Paste in code like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set rng = Range(Cells(1, 1), Cells( _
Rows.Count, 2).End(xlUp)(1, 0))
rng.Formula = "=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"""")"
End Sub

This will probably affect your ability to paste on that worksheet. You
have to look at the trade off of convenience vice functionality.

--
Regards,
Tom Ogilvy


"LoboNetwork" wrote:

Hello all:

I have the following formula in every cell in my "A" column:
=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"")

This serves the purpose so that whenever I type something in column B, it
will auto number the entries consecutively. When i delete a row, the
numbering still works and all the numbers are reorganized but still
consecutive.

However when i insert a row, the formula does not exist in the new cell in
column A.... and so my whole numbering gets thrown off.. Is there a way to
make it such that there is auto numbering when i insert rows? Perhaps by
having the formula above placed in the cell as soon as i insert a new row?
Or something that makes sure each row in column A has a similar formula that
allows this kind of auto-numbering?

Please, Any help would be appreciated,

Terrel Lobo