Help With Serial Number Sequential Orders in a Multiple Serial Number doc

[brsnyder1]

I have a document that I track multiple serial numbers of reports. The trick is that reports initiated by our company have a unique serial number (N00164-18-0001) and reports that are initiated by outside users have a different serial number so the excel spreadsheet has mixed reports going down the list. When I initiate a new report, I have to go back through all the report numbers and verify the next sequential report serial number (N00164-18-0002). I want to make a "Next Report Serial Number" next to my table and I'm looking to see if there is a way to sequentially be able to see what the next report number should be. I only need to track our companies next report serial number. I hope this makes sense and someone has the answer on how to sequentially generate the next serial number even if the report numbers have different serial numbers mixed in with them. Thank you!

 

[brsnyder1]

I have other serial numbers mixed in with these numbers so A4 might not be a N00164-18 serial number. This is why I'm trying to create a seperate box that has the next serial number for N00164 reports so I don't have to go back through the entire list to try to find the last N00164 number

 

[brsnyder1]

This is what I'm looking for but for some reason (probably because im excel dumb) I'm trying to put this formula into D2 but the formula does not leave the cell when I paste it?

 

[jtakw]

Check my updated formula to accommodate your serial starting at N00164-18-5001 in Post # 8, change/adjust cell reference/range to suit your needs.

Edit: we posted at the same time.

Check you Cell Formatting for D2, make sure it's "General" (Not Text), re-enter formula.

And to Copy the VALUE generated by the formula, Copy D2, Paste "VALUE", in Column A cell of your need.

 

[KolGuyXcel]

Let the formula be in D2.

Copy cell D2 and right-click on the cell in column A you wish to enter the value. Use pastespecial and then values in the cell (in Column A) you want to enter the serial number.

 

[brsnyder1]

I've made sure that the formatting for D2 is general but the fomula shows up in cell D2 and does not go away. If i start to type in D3 "N00" then it will have the next serial pop up like the formula is working but the formula is still pasted on the spreadsheet in the cell above it (D2)?

 

[jtakw]

After you've made Certain D2 is formatted General, Re-Enter the formula in D2.

 

[brsnyder1]

Nvm i figured it out. Opened a new Excel document and the formula worked with this one!! Thanks everyone for all the help. Truly appreciated!

 

[jtakw]

Great you figured it out.

Just so you know, my formula will fail After N00164-18-5999, so after that, if you need the Next Serial to be N00164-18-6000, then N00164-18-6001, etc, use this updated formula.
It'll accommodate Any Serial from N00164-18-1000 to N00164-18-9999, if you need it to go even Higher, let me know.

Book1
	A	B	C	D
1	N00164-18-5001			Next Serial Report #
2	R90794-18-0024			N00164-18-5004
3	N00164-18-5002
4	V35053-18-2003
5	N00164-18-5003
Sheet357

Cell Formulas
Range	Formula
D2	="N00164-18-"&RIGHT(LOOKUP(2,1/SEARCH("N00164-18-",A1:A100),A1:A100),4)+1