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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.