Select next number in a range for a specific selection

[Toastis]

I am creating a spreadsheet
Column A - the managers will select their location from a drop down menu. (which I know how to set up),
Column B it would have an indicator if New(N) or Old(O).
Column C is a Serial # column in which there would be a formula that would automatically select the next not already used serial number in their range for that warehouse. If they selected O it would remain blank and they would need to complete that cell with the tote number. If New - it would assign a tote number based on a selected range of numbers. This is a brand new process so all numbers are new and never used before.

What is the best way to accomplish it considering we have warehouse people who are not excel saavy entering this data?

[TABLE="width: 295"]
<tbody>[TR]
[TD]Warehouse[/TD]
[TD]New Or Old?[/TD]
[TD]Tote Serial Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]O[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N[/TD]
[TD]1001[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[kpark91]

As far as how I've understood it,
There is a way to count up in such conditions but not when you only want to work with the cells based on their recent selections.
You would have to use VBA in order to recognize that selection has been made.

In case I have misinterpreted it, this is the way to "assign a number for N case":
Put this in B2 assuming that there is a new serial number in row 1:

=IF(B2 = "N", COUNTIF($B$1:B2, "=N") - 1 + $C$1,  "")

 

[Toastis]

As far as how I've understood it,
There is a way to count up in such conditions but not when you only want to work with the cells based on their recent selections.
You would have to use VBA in order to recognize that selection has been made.

In case I have misinterpreted it, this is the way to "assign a number for N case":
Put this in B2 assuming that there is a new serial number in row 1:

=IF(B2 = "N", COUNTIF($B$1:B2, "=N") - 1 + $C$1,  "")

Thank you for the response - if VBA code could be used I would be open to that. I know just basics and not sure if I could create the VBA code on my own.