I've been asked to set up a shared workbook for use by around 60 people across 2 different countries. I know this is in no way ideal but I need to figure out how to make it work without using a database.

The big issue I have at the moment is that I need to handle conflicts automatically - users enter data onto a row where the only pre-filled cell is a unique reference number. If that row has already been taken I would like to be able to move the user's data down to the next free row, and advise the user of the new reference number. What I can't figure out is if there's a method for doing that.

I was thinking I would use worksheet_change as the event, force users to fill in one particular cell in the row first, and then force a save to check for a conflict as soon as that cell has been filled. What I can't work out is how Excel would tell me if there's a conflict, and how I can use that to set off a bit of code to look for a blank row. Any ideas would be appreciated.