Persistent unique number/row number even after adding and deleting rows.

Hi,

I have a situation that I would like to have a unique row identifier but that does not changes when delete other rows.

Eg: If I use the =ROW() and then delete or insert rows, the row number will be automatically changed and therefore what was first shown in row 12 will be shown in row 11 or 13.

Can you please help?

Thanks

Hi

You could use an Excel-event-routine to "stamp" each "data record" as it is added with a "next-record-ID".

Post a sample file with some dummy example data and we could show you a method or two..

zeddy

Sample file attached.

I would like to have a sheet that when I remove a row the (for example row having unique number 3) unique numbers or the other rows are not re-arranged.

Also if I add a row example for example after " 2 IT Philip Black servers relocation" the unique numbers are not re-arranged and the new row is given the next consecutive unique number....in my case 6.

Thanks a lot.

Hi

Maybe the attached will help as a start point.

The zip-file contains the demo file [persistent-unique-number-zeddy-1.xlsm] - save to a folder of your choice, extract and then load into Excel with macros-enabled.

It uses vba to assign the "next available number" in column A on sheet [Projects].
To prevent any "changes' to existing numbers in column A, simple event-code is used to 'bounce' the cellpointer out of column A (to see the code, right-click on worksheet tab>View Code).

I've added some named ranges on a new sheet (which could be hidden) to allow for dropdown-entries for Department and Employee Name. This uses Data Validation. Also, added some buttons (with macros assigned) for deleting and adding records etc etc etc.

It's just a start - sure others on this Forum could offer help too.

zeddy

Thanks a lot.

I have unprotected the sheet to make some modifications, but cannot manage.

I need a sheet exactly as it is, but instead of add project or delete project.....it says add item or delete item, and that there are no drop down lists, but the user can input what he/she likes.

Thanks and good day.

Hi

I hope the attached will give you what you asked for.
No dropdown lists, and buttons changed to [Add Item] and [Delete Item]

zeddy

Hi

..I also added comments to the vba code to help you follow what's going on.

zeddy

Hi,

It is working ok.

I have edited the below as I have till column K. Also the records are not updating automatically when I copy several rows (already entered 400 rows in another sheet) from another sheet.

'3-column data-record range..
temp = "B" & r & ":K" & r 'e.g. "B17:K17" ; 3 cells in new record

Is there a way to copy items from another sheet and having the records update automatically?

Thanks for your help.