Return next unique number in a range

[JimmyLGS]

Good Morning!

I have created this account specifically to ask this question, so I apologise in advance if I break any rules, guidelines or forum etiquette about posting.

I am looking for help with a spreadsheet that details all of the hire equipment in our company, detailed across multiple sheets within a workbook which are broken down by various product groups. There are 80 sheets in total.

On each sheet in column A there are a list of item numbers for that product group and each item has a unique ID where the first (usually)4 digits are letters, and the next 4 are numeric. Over the years of trading, some numbers have been scrapped and also deleted entirely from our system, meaning the number is essentially vacant.

I'm looking for a formula that will not only identify the next available blank cell in a column, but will also determine what the next number will be.

I've attached a screenshot of an example spreadsheet, where I would want the formula to go in the yellow highlighted cell (B6), and in this instance, return a value of LGAH0005, as this is the next available unique number in the list of A9-A18.

I'm absolutely open to ideas on how to do this a better way, but this is the best way I can think of to establish what your next unique available number is within a sequence at a glance.

Thank you very much in advance, this has been melting my mind so any help will be greatly received!

Best

Jimmy

 

[jasonb75]

For an example of how Jason's formula has been affected by this, I've just created LGBL product group sheet, and there are only two items on there LGBL0001 & LGBL0002.
So, in the 'Next' field, this should ideally be showing me LGBL0003 but I get the #N/A using Jason's formula, and I am sure it is because the 'current available' is 0, owing to the fact that both the items are currently showing 'in service' in column F. 'Current Available' is currently running on a COUNTIF function that I've added to return a value where 'Available' is showing.

I did think that it should be the case but forgot to edit the formula for 'available'. Peter's formula for that is the exact one that I was going to use and will prevent the #N/A errors.

Main thing is, you have a method that works

 

[Peter_SSs]

A very positive first experience on the forum, and will absolutely post again

Thank you all

Good to hear!. You are welcome.