How to automatically number an index column

[Phil]

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.

 

[Rob Crawford]

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.

Hi Phil.

Two suggestions.
#1 - use a database, with an autonumber primary key and the query the
database from excel for calculations/ pivot tables, etc. The advantage of
this is the database will always increment up the ID field autonumber, so you
can see also if numbers have been deleted.
#2 - use a vba form to allow additions only to a protected "hidden" sheet
within the workbook. All this means is that users add data by way of a form,
this form will only write to the next blank space down, you cannot (as a
casual user) see, or change the data on this "protected" sheet, only the
"admin" for the file can do that.

Personally i'd go for a database, you can have multiple users at once, and
you're not limited to excel's 65K lines maximum. You should be able to
import any data you already have into an access database, straight from
excel, there's a wizard for it in access.

Regards,
Rob.

 

[Phil]

Hello,

I didn't realize until it was too late that the last Index_No on Row-6
should = 6, not 5.

Thanks.

 

[Phil]

Hi Rob,

With respect to #1: I would love to move this application over to an Access
database, but TRUST ME, that WILL NOT happen (believe me, I tried). So I
must move on, and try to work with what the folks I working with now.

With respect to #2: A form sounds good, but I have NO experience with VBA.
However, your point is a good one, and that is that I should re-post this in
the VBA NG.

Thanks for your replies, though.

Regards,

Phil.

 

[DOR]

Create a new column, say D, so as to keep it adjacent to your exiting
columns for later sorting. Label it TractStrip, or some such alpha
name so as to keep it in the current (sort) region, and allow Excel to
infer that a header row exists. Place the following formula in D2 and
copy down.

=VALUE(SUBSTITUTE(B2,"-",))

Place the following formula in A2 and copy down

=RANK(D2,OFFSET($D$1,1,0,COUNT(D)),TRUE)

Sort when necessary.

It may be possible to dispense with col D and use an array formula, but
I haven't time to work that one out ...

 

[Richard Buttrey]

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

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
__________________________

 

[Richard Buttrey]

Hi, me again,

Just seen your response where you say you're not familiar with VBA

Copy the code within the Sub....End Sub procedure I've just posted.
i.e. the following, to the clipboard.

Target = ActiveCell.EntireRow
If Not Intersect(Target, ActiveCell) Is Nothing Then
ActiveCell.EntireRow.Cells(1, 1) = "=row()-9"
End If

Now go to the sheet you're interested in and
Right Click on the sheet name tab then choose
View Code

You should find a VBA window opens up which shows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Paste the code from the clipboard between the two lines above and
close the VBA window.

The code should work as described in my previous posting.

HTH

Hi Rob,

With respect to #1: I would love to move this application over to an Access
database, but TRUST ME, that WILL NOT happen (believe me, I tried). So I
must move on, and try to work with what the folks I working with now.

With respect to #2: A form sounds good, but I have NO experience with VBA.
However, your point is a good one, and that is that I should re-post this in
the VBA NG.

Thanks for your replies, though.

Regards,

Phil.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

Hi, me again,

Just seen your response where you say you're not familiar with VBA

Copy the code within the Sub....End Sub procedure I've just posted.
i.e. the following, to the clipboard.

Target = ActiveCell.EntireRow
If Not Intersect(Target, ActiveCell) Is Nothing Then
ActiveCell.EntireRow.Cells(1, 1) = "=row()-9"
End If

Now go to the sheet you're interested in and
Right Click on the sheet name tab then choose
View Code

You should find a VBA window opens up which shows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Paste the code from the clipboard between the two lines above and
close the VBA window.

The code should work as described in my previous posting.

HTH

Hi Rob,

With respect to #1: I would love to move this application over to an Access
database, but TRUST ME, that WILL NOT happen (believe me, I tried). So I
must move on, and try to work with what the folks I working with now.

With respect to #2: A form sounds good, but I have NO experience with VBA.
However, your point is a good one, and that is that I should re-post this in
the VBA NG.

Thanks for your replies, though.

Regards,

Phil.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Phil]

Hi,

I was able to get the first part right =VALUE(SUBSTITUTE(B2,"-",)), except I
had to infer that your B2 reference was actually supposed to be A2, right?
Because it is the A column that will contain the Indexes. But when I tried
to do =RANK(D2,OFFSET($D$1,1,0,COUNT(D)),TRUE), my first thought was any
reference to any Row1 is going to present a problem, because that row is
being used by the col. headings.

The formula (when typed in) returns a 0 (zero) in cell A2.

What am I doing wrong?

Thanks.

Phil.

 

[Phil]

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.

 

[Phil]

I realized that after reading my requirement parameters for this post, that I
will need to change it up a bit. As I said earlier, I will be getting the
SAME spreadsheet back each week. And I want to be able to see how many
changes have been done, hence the indexing idea.

What I need instead, is after the user enters a new row for the record, the
routine finds the last existing index number, then increment from the last
number +1,
and place it in the blank Index_No cell next to the new record.

So, it would be like this:

Index_No
row2 1
....
row526 525

And if the user wanted to insert a new record at say, row 500, then the new
Index_No for that record would be 526.

That way, when I get the spreadsheet back, I can see instantly (after doing
a sort) where all the new records are.

I hope that you can understand that.

Let me know if you need more info.

Phil.

 

[Richard Buttrey]

Hi Phil,

Try just

If Not Intersect(Target, ActiveCell) Then
ActiveCell.EntireRow.Cells(1, 1) = "=row()-9"
End If

i.e. remove the "Is Nothing"

I also forgot to add that if the index column is not column B, but say
column C, the third column, you'd need to change the "Cells(1,1)"
"Cells(1,3)"

Rgds

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
Grappenhall, Cheshire, UK
__________________________

 

[DOR]

Phil,

Thank you for clearing up the requirements. You can ignore my previous
post - it satisfied the wrong problem (although I had coded it
correctly for the problem it did solve!). The following approach is
based on Richard's solution, but does things a little differently. It
dispenses with the index column, and assumes the Tract ID is entered in
column A and the Parcel ID in column B. It puts the User's name in
column C and the Date in column D whenever a cell is changed. (I am
assuming that might be a preferable solution - if you want to stick
with the next higher Index Number approach, that can be done too.)

Go to the same VBA sheet in which you entered Richard's code and
copy/paste the following instead (you will need to delete Richard's
code):

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveCell.EntireRow.Cells(1, 3) = Application.UserName
ActiveCell.EntireRow.Cells(1, 4) = Date
Application.EnableEvents = True
End Sub


You can now use autofilter to determine which entries have been made
(or changed) since a specific date, and by whom. You can change the
columns in which the name and date are entered by changing the second
number in the statement that enters each item.

The macro could be refined to operate only when the Tract ID is
changed, if you wish.

Note that this macro is activated only when a cell is changed in the
worksheet; Richard's macro was activated whenever a cell was selected.
This difference is indicated by the different first lines in each
macro. Make sure you copy this macro correctly, because if it fails
after the "Application.EnableEvents = False" is executed and before
"Application.EnableEvents = True" statement is executed, you will need
to restart Excel to re-enable event handling (on which this depends).

Good luck.

 

[DOR]

OTOH, if all you want to do is identify the new entries, why not take a
really simple approach.

Use a column to contain an indicator of the fact that you have reviewed
an entry, say the date of your review or your initials or whatever.
Then hide that column when you distribute the spreadsheet. When you
receive it back, display that column. All the new entries will be
blank in that column. Autofilter that column on blanks. Now you will
see all the new entries, and only the new entries. Then, after you
review them, enter your "reviewed" indicator in the first entry and
drag it down through all the visible rows; only the visible rows will
be changed. Sort the table, hide the column again and redistribute ...

Use protection as necessary to prevent others changing that column.