I am making a chemical inventory spreadsheet of about 150 different chemicals in my lab. Each chemical bottle get its own row with a column for name, location, hazard, date acquired, etc.
The top row consists of drop down lists to allow for auto sorting by any category.
Now:
I want to also make a unique label for each individual chemical bottle that gets generated in the excel sheet so that we can print a label for each bottle to match to the inventory. This would ideally be a concatenated string of something like "location-hazard-####" The numbers are required because we have multiples of certain chemicals and I originally tried creating numbers in two parts such that if "chemical name" = "chemical name in column above" then the "number" would be "1 + the number above", and concatenate that with the same procedure for location, BUT I realized that I cannot have cell references to other rows because they will all change depending on how we have the columns auto sorted!

Is there a way to have a generated number that becomes permanent as soon as the function is added and also as soon as any new chemical is added? Any other ideas?

(This is a university lab, so the goal is to make something that requires as little management as possible from year to year so that when new students come in they don't have to redo/learn anything, just add and remove chemicals as they arrive or are used, and print new labels. Otherwise I would just manually create the numbers and labels)

Thank you for your help!