Next Available Number in Range

[Marhier]

Good morning, I hope you're all well today.
I'm trying to find a formula that will give me the next available sequential number in a range of cells.

Use case:
I have an order register with a list of sequential schedule numbers (Column A).
Once an order has been placed, I put the order number in the respective cell in Column O.
I might order schedule 024, before I order schedule 016, so the order numbers aren't typically listed in sequential order.

What I'm After:
In cell V5, I want to put a formula that looks that looks at range O9:O1008 (or PurchaseOrderTable[Order Ref 2] - table reference) and tells me what the next number in the sequence is.

Potential Issue:
Sometimes, an N/A gets put in one of these cells if a schedule has been cancelled, or placed on a credit card, so worried the moment a text value gets entered, it'll throw that formula out.

Any help would be greatly appreciated.
Thank you.
Regards
Marhier.

 

[jasonb75]

It should be as simple as

=MAX(PurchaseOrderTable[Order Ref 2])+1

 

[Marhier]

Thanks Jason.
The issue I'm having with this solution is when someone put's an "N/A" in a cell from the order number column, it returns a #VALUE!

Any way to avoid this?

Much appreciated.
Thank you.
Regards
Marhier.

 

[jasonb75]

There is no reason for that to happen, it works fine for me.

Book1
	O	P	Q
4	Order Ref 2
5	1		7
6	2
7	N/A
8	4
9	5
10	6
Sheet14

Cell Formulas
Range	Formula
Q5	Q5	=MAX(Table1[Order Ref 2])+1

 

[Marhier]

That's strange.
See attached image - have I done something wrong?

Regards
Marhier.

 

[jasonb75]

have I done something wrong?

Yes, you've changed the formula. It is not the same as the one in my post.

 

[Marhier]

The way I did it above, gives me the result I want, but fails when I put an "N/A"
The way you suggested, just displays a "1" and doesn't give me the correct result.

See attached:

To clarify, I'm on Excel (Office 365).

 

[jasonb75]

Then it means that your numbers are formatted as text. You need proper numbers for formulas to work.

Looking at your screen capture, you may also have a problem with the dates in columns H, J, and R. They might look fine but if they are in text format then formulas will not recognise them and it will be absolute carnage if you try to sort the table using one of those columns.

 

[Marhier]

Ah, you're right!
I think I did this becuase I wanted to keep the leading zeros, due to the last 4 digits of our order numbers being 4 characters.
I should have formatted to custom and then put 0000 in the type!

This is now working.

I can confirm all my other cells are formatted correctly, lol!

Thanks mate.
Regards
Marhier.

 

[jasonb75]

I can confirm all my other cells are formatted correctly,

The giveaway is the alignment of the cells. Typically anything that uses a valid numeric format will be aligned to the right of the cell, text will be aligned to the left. While it is never a certainty because people do change the alignment, it is often a good indicator in a screen capture.

Glad you got it working, I had just started typing up a 'how to' guide to covert everything to valid numbers but as with many things, it's easier to do than to explain.