So basically, what I'm trying to do in Google Sheets, is:
I have a column called 'Name' and a column called 'Badge number'. Badge number consists of 2 parts: category and increment number.
For instance: there is a badge P05-008, where P05 is a category and 008 is just a number in that category. There might be P05-008 and P06-008 badges (so the second number is not unique). This category (1st part) depends on a department, for instance if the department is TD, then category would be P05-xxx. The department name is added to another column.
What I do now: manually add a new name and searching for the last free number to add a badge number.
What I want to do: manually add a new name and Google automatically fills in the badge number, using the last free badge number. For example, if there already are badges P05-006 and P05-008, then the new badge automatically would be P05-007, not P05-009.
Right now, to make my life easier, I created a conditional formatting that highlights the badge cell with red if there already is a badge with the same number.
So, my question is: is it possible to to with Google Spreadsheets or not? And sorry for my long description. There's an image: