Unique IDs for records run each week

[inntheboat]

We have our new employees fill out a form with name, address, tax info, etc. each week. (They all do it at the same time.) I then get 20 PDFs and export all the data to a CSV. We now want to import that CSV into our payroll software. However, the payroll software requires a unique ID for each employee.

So, we'll have this CSV each Monday, and each Monday, I need to "continue" the unique ID. So if this past Mondays started with 1000 through 1020, then next week, when I export, I want to start those records at 1021 and end at 1040. Then the following Monday, 1041-1060, etc.

Is there a way I can keep a record of what IDs are used and continue from that point each week?

 

[excelbee2013]

May be you need to maintain a simple master sheet where you can add an column to auto increment value as Id.

 

[inntheboat]

That's kind of what my thought was. I was trying to think of how I could maintain a database in Access or something, but I don't quite know what the process would be. I also thought about keeping just an Excel sheet and having a macro append the new records to the "master Excel workbook." I'm working on that process now, but can't seem to figure out how to reference an absolute path in VBA.

 

[excelbee2013]

Good Luck. Hope you will do it successfully. In case need help, do not hesitate to write here back.