Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get VBA to automatically generate Column Index numbers
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 recommendations). 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 4 8-5-012-005 00005147 Row-6 5 8-6-030-125 00001541 If you need more information, please let me know. TIA for your replies. Phil. BTW, if you are noticing that this was posted in another MS NG, you'd be correct, but I have decided to post here as well, as one of the respondents commented that this might be better done with VBA. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
HOW TO AUTOMATICALLY GENERATE NUMBERS IN EXCEL | Excel Discussion (Misc queries) | |||
automatically generate unique serial numbers for invoices | Excel Worksheet Functions | |||
Auto generate numbers in decending order in a column | New Users to Excel | |||
How to automatically number an index column | Excel Worksheet Functions |