# Next Number In Sequence?



## Elliot46 (Dec 19, 2022)

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


----------



## Flashbond (Dec 19, 2022)

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 (Dec 19, 2022)

Hi & Welcome to MrExcel.

Something like?

Book7AB12Next Drawing No.3D000445Drawing No6D00017D00028D0003910111213Sheet2Cell FormulasRangeFormulaA3A3="D000"&COUNTIF($A$6:$A$13,"*")+1


----------



## Flashbond (Dec 19, 2022)

Sufiyan97 said:


> Hi & Welcome to MrExcel.
> 
> Something like?
> 
> Book7AB12Next Drawing No.3D000445Drawing No6D00017D00028D0003910111213Sheet2Cell FormulasRangeFormulaA3A3="D000"&COUNTIF($A$6:$A$13,"*")+1


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


----------



## Sufiyan97 (Dec 19, 2022)

Flashbond said:


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



Then how about

Book7AB12Next Drawing No.3D001445Drawing No6D00017D00028D00039D000410D000511D000612D000713D000814D000915D001016D001117D001218D001319Sheet2Cell FormulasRangeFormulaA3A3="D"&TEXT(COUNTIF($A$6:$A$22,"*")+1,"0000")


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


----------



## Flashbond (Dec 19, 2022)

Sufiyan97 said:


> Then how about
> 
> Book7AB12Next Drawing No.3D001445Drawing No6D00017D00028D00039D000410D000511D000612D000713D000814D000915D001016D001117D001218D001319Sheet2Cell FormulasRangeFormulaA3A3="D"&TEXT(COUNTIF($A$6:$A$22,"*")+1,"0000")
> 
> ...


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


----------



## jdellasala (Dec 19, 2022)

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).Book1AB1Drawing No.Drawn By2D0001Kian3D0002Peyton4D0003Sophia5D0004Poppy6D0005Talon7D0006Damon8D0007Sebastian9D0008Janessa10D0009Jarvis11D0010Jayla12D0011Micah13 14#VALUE!Who?Sheet1Cell FormulasRangeFormulaA2A2="D"&TEXT(1,"000#")A3:A14A3=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 (Dec 20, 2022)

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


----------



## Flashbond (Dec 20, 2022)

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 (Dec 20, 2022)

You could use something like:


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

assuming no blank cells _within_ the data range.


----------



## Elliot46 (Dec 19, 2022)

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


----------



## Sufiyan97 (Dec 20, 2022)

Hi Rory

Why formula does not work without "\"


----------



## RoryA (Dec 20, 2022)

Because D is a built-in custom format code (for day) so it needs to be escaped to be a literal D.


----------



## Sufiyan97 (Dec 20, 2022)

RoryA said:


> Because D is a built-in custom format code (for day) so it needs to be escaped to be a literal D.



Thanks for the info.

one more
Does "\" always used to specify the format?


----------



## Flashbond (Dec 20, 2022)

Oh, also I didn't know that. Then my formula needs "\", too..


----------



## RoryA (Dec 20, 2022)

Sufiyan97 said:


> Does "\" always used to specify the format?


\ is an escape character for the character that follows it. You can also enclose them in quotes - since the format string is already in quotes for the TEXT function, you'd need to double them so using \ was easier.


----------

