Generate next available project number in a sequence

Question

Hi,

I'm hoping someone can help me with a formula. I want to generate the next available number in the sequence with the last four digits being the year. Thank You!

Answer 1

Assuming that column E is completely empty below the last RTO #:

=LET(last, LOOKUP("z", E:E), seq, LEFT(last, 3), y, RIGHT(last, 4), yy, TEXT(TODAY(), "yyyy"), newseq, IF(y=yy, TEXT(seq+1, "000"), "001"), newseq&"-"&yy)

Answer 2

Dear respected B M_0312,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

As per your description, try this:

=LET(last, LOOKUP(2, 1/(A:A<>""), A:A), seq, LEFT(last, FIND("-", last) - 1), y, RIGHT(last, 4), yy, TEXT(TODAY(), "yyyy"),newseq, IF(y=yy, TEXT(VALUE(seq) + 1, "000"), "001"), CONCAT(newseq, "-", yy))

If there is any misunderstanding, I apologize and please feel free to post back to us with more detailed information for better understanding and guiding you further.

Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely,

Waqas Muhammad

Answer 3

Hi respected B M_0312,

I'm writing a follow up this case, and I haven’t received any information from you, may I know have you checked reply? Feel free to post back if you need further assistance.

Have a good day and stay safe 😊

Sincerely,

Waqas Muhammad