Return Number with -

[noraa]

Hello All,

I need this formula to return a 5 digit number with a - and 2 digits it is matching column A on sheet 2
=IFERROR(MAX(OrderNumb)+1,1)


In Column a It reads 24001-1
24001-2
And continues till the end
I would like it to return 24001-3

I have tried formatting both the Cell a column 2 is formatted as ?????-??also tried #####-## no luck.
Any help would be appreciated no VBA please need the formula.

 

[kweaver]

Please use XL2BB to post examples of your data and expected results.

 

[noraa]

Hello,

B6 Sheet1 contains the formula it needs to return the next number sequnce from column A Sheet 2 24001-3. I can only get it to return Number 1 in cell B6 Sheet1

 

[Sufiyan97]

Try

Confirm formula with ctrl+ shift + enter

Book3
	A	B	C	D	E	F
1			Order #
2			24001-1
3			24001-2
4
5
6	Next Order
7	24001 - 3
8	24001 - 3
9	- 3
10	- 3
11	- 3
12	- 3
13	- 3
14	- 3
15	- 3
16
Sheet1

Cell Formulas
Range	Formula
A7:A15	A7	=LEFT(C2:C10,SEARCH("-",C2:C10&"-")-1)&" - "&MAX(IFERROR(MID(C2:C10,SEARCH("-",C2:C10&"-")+1,99)+1,0))
Dynamic array formulas.

 

[noraa]

I've tried changing the formula so that it calls the value from sheet 2 Column A but it keeps giving me a name error.

=LEFT(Sheet2.Range(A3:A10),SEARCH("-",Sheet2.Range(A3:A10)&"-")-1)&" - "&MAX(IFERROR(MID(Sheet2.Range(A3:A10),SEARCH("-",Sheet2.Range(A3:A10)&"-")+1,99)+1,0))

Pretty new to all this. not sure what I am doing wrong or missing?

 

[noraa]

This is my first Sheet1_ Order. I need cell B6 to increment by 1 number I if there is a blank on Sheet2- Order List Column A3 and increment through Sheet2-Order list column A. Each time an order is addede B6 Will Update to that new order Number plus 1 to the right of the hyphen

1Orderform populate working3-28-2023.xlsm
	A	B	C	D	E
2	Distributor Row	5		P.O. #:	24001-2
3	Order Row	4		Job Name
4	New Order	FALSE		Distributor	J+J
5	Order Load	FALSE		Quote #	7
6	Next Order #	=LEFT(Sheet2.Range(A3:A10),SEARCH("-",Sheet2.Range(A3:A10)&"-")-1)&" - "&MAX(IFERROR(MID(Sheet2.Range(A3:A10),SEARCH("-",Sheet2.Range(A3:A10)&"-")+1,99)+1,0))		Account #	12
Order

Cell Formulas
Range	Formula
B2	B2	=IFERROR(MATCH(E4,Distributors_Name,0)+2,"")
B3	B3	=IFERROR(MATCH(E2,OrderNumb,0)+2,"")

Cells with Data Validation
Cell	Allow	Criteria
E4	List	=Distributors_Name

Since the last number in Sheet 2 A is 24001-2 The next Number that should be in B6 is 24001-3

1Orderform populate working3-28-2023.xlsm
	A	B	C	D
1	Order List
2	Order #	Date	Distributor	Account #
3	24001-1
4	24001-2	3/28/2023 8:47	J+J	7
Order List

My Apologies for not using mini sheets earlier it took me a while to figure it out.

Thank you

 

[Sufiyan97]

See if this works

Confirm formula with Ctrl + Shift + Enter

Order Sheet

Book6
	A	B	C	D	E
1	Distributor Row			P.O. #:	24001-2
2	Order Row			Job Name
3	New Order	FALSE		Distributor	J+J
4	Order Load	FALSE		Quote #	7
5	Next Order #	24001-3		Account #	12
Order

Cell Formulas
Range	Formula
B1	B1	=IFERROR(MATCH(E4,Distributors_Name,0)+2,"")
B2	B2	=IFERROR(MATCH(E2,OrderNumb,0)+2,"")
B5:B12	B5	=LEFT('Order List'!$A$3:$A$10,SEARCH("-",'Order List'!$A$3:$A$10&"-")-1)&"-"&MAX(IFERROR(MID('Order List'!$A$3:$A$10,SEARCH("-",'Order List'!$A$3:$A$10&"-")+1,99)+1,0))
Dynamic array formulas.

Order List

Book6
	A	B	C	D
1	Order List
2	Order #	Date	Distributor	Account #
3	24001-1
4	24001-2	45013.37	J+J	7
Order List

 

[noraa]

Thank you Sufiyan97,

This formula is returning a -0 which is better than what I was getting but it is not returning a 5 digit incremented # From Column A Sheet 2

 

[Sufiyan97]

Thank you Sufiyan97,

This formula is returning a -0 which is better than what I was getting but it is not returning a 5 digit incremented # From Column A Sheet 2

Have you entered formula with Ctrl + Shift + Enter?


Another Option

Book6
	A	B	C	D	E	F
1	Distributor Row			P.O. #:	24001-2
2	Order Row			Job Name
3	New Order	FALSE		Distributor	J+J
4	Order Load	FALSE		Quote #	7
5	Next Order #	24001-3		Account #	12
6
7
Order

Cell Formulas
Range	Formula
B1	B1	=IFERROR(MATCH(E4,Distributors_Name,0)+2,"")
B2	B2	=IFERROR(MATCH(E2,OrderNumb,0)+2,"")
B5	B5	=REPLACE(INDEX('Order List'!$A$3:$A$10,COUNTIF('Order List'!$A$3:$A$10,"*")),SEARCH("-",INDEX('Order List'!$A$3:$A$10,COUNTIF('Order List'!$A$3:$A$10,"*")))+1,99,MID(INDEX('Order List'!$A$3:$A$10,COUNTIF('Order List'!$A$3:$A$10,"*")),SEARCH("-",INDEX('Order List'!$A$3:$A$10,COUNTIF('Order List'!$A$3:$A$10,"*")))+1,99)+0+1)

 

[noraa]

Thank you Sufiyan97,

Works Great, I was starting to pound my head against the wall. Thank you so much!