Automatically generate item or ID#'s
Automatically generate item or ID#'s
(OP)
I use EXCEL to keep track of punchlist items or certain tasks.
How can I get EXCEL to automatically generate in an ID# or item number in a column "A" and keep it with the task in column "B"(without changing if items are added or deleted, etc.)
How can I get EXCEL to automatically generate in an ID# or item number in a column "A" and keep it with the task in column "B"(without changing if items are added or deleted, etc.)





RE: Automatically generate item or ID#'s
When a punchlist item is complete is it and the task
deleted or struck through?
If it is deleted (or if you manually blank it out)
are you wanting to move everything up?
We can help if you are a little more specific in how you do it now and what you anticipate any automated routine to accomplish for you.
RE: Automatically generate item or ID#'s
Read the Eng-Tips Site Policies at FAQ731-376
RE: Automatically generate item or ID#'s
Here's how I did it. Create a brand new sheet which I called "variables_sheet". In cell A1 of variables_sheet is the number 1. In cell A2 of the variables_sheet is the formula +A1+1.
Then go to sheet1 where the data will be stored including the serial number. Put your cursor in any empty cell and start macro recording. Go back to variables_sheet, select cell A1 (initially contains 1) and ctrl-c to copy. Now go back to sheet1 (the cell that you started in should already be highlighted) and press ctrl-v to copy the serial number (1) into the cell. Now once more go back to the variables sheet. Place cursor in cell A2 and ctrl-c top copy. Move to cell A1 and Edit/Paste-Special/Values. (This increases A1 by 1 and A2 remains A1+1). Now go back to the sheet1. For some reason at this point I had to press escape to convince excel I wasn't trying to paste something. Now end marco recording.
Now that the macro is recorded, with your cursor anywhere in sheet1, invoke the macro and it will put your serial number into that cell and increment the "counter" in variables_sheet.
Here is the vba code that excel created when I created this macro. Don't ask me what it means (I don't do vba or other psychedelic drugs)
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 9/27/2005 by Terry
'
'
Sheets("variables_sheet").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("variables_sheet").Select
Range("B1").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
End Sub
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Automatically generate item or ID#'s
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 9/27/2005 by Terry
'
'
Sheets("variables_sheet").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("variables_sheet").Select
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
End Sub
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Automatically generate item or ID#'s
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Automatically generate item or ID#'s
==============================================
*Items would not be inserted, only added beneath the bottom (last item.
*Items will not be deleted, only documented (in a separate column as completed or open.
*The ID# doesn't have to be anything complicated. Simply 1,2,3,4...etc. would be fine. The main thing is to NOT have any duplicate # or have them change with the punchlist item they are linked to (yeah, it's better suited to a database, but everyone seems to have EXCEL and it seems easiest.)
RE: Automatically generate item or ID#'s
If it is a trivial matter of just having a unique number associated with each row, just enter 1 in cell a1, and =a1+1 in cell a2, then select cell a2:a60000, and press control-d ... (you can the copy and paste the ID numbers as valuse if you really don't want them to change) ... but I doubt what you want is that trivial.
Sorry, I'm just a bit confused as to what exactly you are trying to do. Electricpetes suggestion will generate a unique ID number for a particular cell in a particular workbook, but if you want to generate lots at once, or want to do so over multiple workbooks, you will have to modify his code to put the variables worksheet in your personal excel worksheet so it is available to all workbooks.
Read the Eng-Tips Site Policies at FAQ731-376
RE: Automatically generate item or ID#'s
(start the list on row #2, headers on row #1 are okay)
1 Insert a new column (C1, or Column A) for the enumeration
2 Enter '1' at C1R2 and '2' at C1R3 (this sets up an informal '+1' pattern in C1 (Column A))
3 Select c1r2:c1r3 (a2:a3; so '1' and '2' are highlighted)
4 Click-and-drag on the small square in the lower right corner of the highlight box and drag it down; the '+1' pattern will repeat for as far down as you drag it. You now have fixed numbers for each entry that will not change if rows are inserted or deleted
At this point when I add rows I manually place the number for the new row (or use the above technique to continue numbering multiple rows).
5 To let me know what highest number used so far is, I put something like this in C1R1:
=CONCATENATE("Max is ",MAX(A2:A65535))
In this example that function will show '2' so that I know '3' is the next value. This comes in handy for situations where you end up inserting an entry in the middle of the existing rows.
Good luck.
RE: Automatically generate item or ID#'s
each meeting is copied into a new worksheet.
items that are closed: line is autoformated to gray based on the contents of the column "status".
use AUTOFORMAT to color gray if status = closed
red if status = delayed
green if status = in progress
yellow if status = stand-by
to answer the original question about how to create item numbers...
this formula generates an id number for each new line added at the bottom
=row()-# of header rows
assuming the data start on row 5, because rows 1 thru 4 are header rows
= row()- 4
hth
saludos.
a.