Hello Richard,

Your solution worked fine, except that when I move my mouse cursor over
another cell, the contents of that cell are deleted.

Why might that be?

BTW, I changed the decrement to be -1 instead of -9, in the VBA code, and
the numbering went perfectly.

Thanks,

Phil.

"Richard Buttrey" wrote:

Hi,

Enter
"=Row()"

in your column A and copy down all appropriate rows.
You may need to add a constant if your data does not start in row 1.
i.e. if data starts in row 10 make it "=Row()-9"

Now put the following code in the Worksheet_SelectionChange event.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target = ActiveCell.EntireRow
If Not Intersect(Target, ActiveCell) Is Nothing Then
ActiveCell.EntireRow.Cells(1, 1) = "=row()-9"
End If
End Sub

Now whenever a new row is added, all rows will be re-indexed
immediately, and as soon as the user selects a cell anywhere on the
sheet, the index for the new row will be added.

HTH


On Mon, 24 Oct 2005 07:19:15 -0700, "Phil"
wrote:

Hello,

I am working with a group of individuals that will be passing around an
excel spreadsheet to one another, and wanted to come up with a way to have
the 1st column act as an index, with the key component requiring that the
index column would automatically re-number itself, if someone entered a new
row.

A typical value in the first column looks like this: 8-5-012-005

Which in our case means that there are 4 series of number sets, separated by
dashes. So the first set is 8, the second 5, the third 012, and the fourth
005. The available range for the sets are 7 or 8 for the first, 5 through 9
for the second, and 0 through 130, and 0 through 200, respectively.

So the user can pick any of these ranges for when they decide to create a
new record (row).

Here is the way the spreadsheet columns currently look (always sorted by
Tract_ID):

Row-1 Tract_ID Parcel_ID
Row-2 7-5-065-105 01245787
Row-3 7-5-112-005 01245787
Row-4 8-5-012-005 01245787
Row-5 8-6-030-125 01245787

Now, here is the way I'd like to have the spreadsheet columns look with the
Index_No (can be either Numeric or Text - depending on your recomendations).
The sort order is based on 1st, the index number, then 2nd the Tract_ID:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-112-005 0126A560
Row-4 3 8-5-012-005 01005147
Row-5 4 8-6-030-125 01000541

Then, let's say the user wants to enter a new value like say, 7-5-105-021.
That value would need to go between Row-1 and Row-2, which, if they just
inserted the value in the row of their choice, would screw up the indexing.

What I need is a way to ALWAYS create an index (automatically), no matter
where they decide to put the value in the spreadsheet, AND it would update
all of the other Indexes as well (very important requirement).

So the end result would be this:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-105-021 00547419
Row-4 3 7-5-112-005 5126A560
Row-5 5 8-5-012-005 00005147
Row-6 5 8-6-030-125 00001541

Please let me know if I need to provide more information to help you with
giving me an answer.

TIA for your replies.

Phil.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________