Next Number In Sequence?

[Elliot46]

Hi All,

New here and new to excel really... I had a question & don't know if it is something easily solved.

Below is a screen grab of a work drawing list with drawing numbers as per below example... D0001, D0002, D0003 etc.

Is there anyway to get the cell below (Next Drawing No.) to automatically update with the next number in the sequence, after D0003 respectively is entered into a cell? Taking into consideration that previous numbers
will be entered frequently with letters a, b, c etc behind them for drawing revisions.

Thank you for any help in advance.

Regards,
Elliot

 

[Legacy 143009]

Assuming your data is in column D:

="D"&TEXT(RIGHT(LOOKUP(2,1/(D:D<>""),D:D), LEN(LOOKUP(2,1/(D:D<>""),D:D))-1)+1,"0000")

Or

=TEXT(RIGHT(LOOKUP(2,1/(D:D<>""),D:D), LEN(LOOKUP(2,1/(D:D<>""),D:D))-1)+1,"D0000")

I haven't tested the second one.

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Something like?

Book7
	A	B
1
2	Next Drawing No.
3	D0004
4
5	Drawing No
6	D0001
7	D0002
8	D0003
9
10
11
12
13
Sheet2

Cell Formulas
Range	Formula
A3	A3	="D000"&COUNTIF($A$6:$A$13,"*")+1

 

[Legacy 143009]

Hi & Welcome to MrExcel.

Something like?

Book7
	A	B
1
2	Next Drawing No.
3	D0004
4
5	Drawing No
6	D0001
7	D0002
8	D0003
9
10
11
12
13
Sheet2

Cell Formulas
Range	Formula
A3	A3	="D000"&COUNTIF($A$6:$A$13,"*")+1

This will cause the number displayed like D00010 with excess leading zeros after larger numbers.

 

[Sufiyan97]

This will cause the number displayed like D00010 with excess leading zeros after larger numbers.

Then how about

Book7
	A	B
1
2	Next Drawing No.
3	D0014
4
5	Drawing No
6	D0001
7	D0002
8	D0003
9	D0004
10	D0005
11	D0006
12	D0007
13	D0008
14	D0009
15	D0010
16	D0011
17	D0012
18	D0013
19
Sheet2

Cell Formulas
Range	Formula
A3	A3	="D"&TEXT(COUNTIF($A$6:$A$22,"*")+1,"0000")

Anyways you provided a great formula, it is updating based on last number entered.

 

[Legacy 143009]

Then how about

Book7
	A	B
1
2	Next Drawing No.
3	D0014
4
5	Drawing No
6	D0001
7	D0002
8	D0003
9	D0004
10	D0005
11	D0006
12	D0007
13	D0008
14	D0009
15	D0010
16	D0011
17	D0012
18	D0013
19
Sheet2

Cell Formulas
Range	Formula
A3	A3	="D"&TEXT(COUNTIF($A$6:$A$22,"*")+1,"0000")

Anyways you provided a great formula, it is updating based on last number entered.

Yes, if the numbers increase squentially, then your suggestion is better.

 

[jdellasala]

Gotta say, the number of good responses shows not just how flexible Excel is, but also how valuable this board is!
Here's a solution you can copy down the entire column, and the appropriate Drawing No. when Drawn By is entered (must be in sequence).

Book1
	A	B
1	Drawing No.	Drawn By
2	D0001	Kian
3	D0002	Peyton
4	D0003	Sophia
5	D0004	Poppy
6	D0005	Talon
7	D0006	Damon
8	D0007	Sebastian
9	D0008	Janessa
10	D0009	Jarvis
11	D0010	Jayla
12	D0011	Micah
13
14	#VALUE!	Who?
Sheet1

Cell Formulas
Range	Formula
A2	A2	="D"&TEXT(1,"000#")
A3:A14	A3	=IF(B3<>"","D"&TEXT(NUMBERVALUE(RIGHT(A2,4)+1),"000#"),"")

I included Row 14 to show it's not infallible! Cell A2 can also just contain "D0001". Used the TEXT function just to illustrate its output.

 

[Elliot46]

Good afternoon all,

Thank you for your responses, all very much appreciated. I am a complete novice when it comes to this.

So my drawing No. data is in column B. It will have older / previous numbers in there quite frequently... is this a spanner in the works in terms of a formula being able to keep track of the drawing number sequence?

Regards,
Elliot

 

[Legacy 143009]

I haven't tested. Hope it works:

=LET(n,RIGHT(LOOKUP(2,1/(B:B<>""),B:B),TEXT(IF(ISNUMBER(n,1)*1),RIGHT(n, LEN(n)-1),MID(n, 2, LEN(n)-2))+1,"D0000")

 

[RoryA]

You could use something like:

=TEXT(MAX(MID(B19:B27,2,4)+0)+1,"\D0000")

assuming no blank cells within the data range.