Creating a Stock Management System with unique ID's - preventing duplicate ID's

Hello All,

I am a newbie to this forum and in all honesty I've registered because I'm struggling to find some help on a particular issue I have.

I am already quite proud of myself as I've progressed reasonably well in creating a stock management system using Excel. Ok, it's probably not the best in the world, however it helps us to pick an item of stock off the shelf, stick a reference label on it and then enter that reference label into Excel and populate attributes against each stock item.

The one thing I'm trying to do is that when a reference label is selected from a dropdown, e.g. AA001, then I want it to disappear as an option to prevent the ability to re-select a reference label already used. I have used the prevent duplicate message for now, however that is more a reactive than proactive solution in that it still allows you to select the duplicate from the dropdown before telling you it's already used.

I would be really grateful if anyone could point me in the right direction for the solution above, including how to make the dropdown dynamic so we can keep adding more stock labels as time progresses (e.g. AA001-AA999, to ZZ001-ZZ999).

I look forward to any assistance

Many thanks,

digital_singh