• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Cell Increment Contain Text and Number

cyliyu

Member
I will need some help on what should be the formula to use.
I want column D to auto-increment when the date in column C filled.
19 represent 2019 and 20 represent 2020. The running number will start from 0001 when the year changed.
Thanks.

71526
 

Attachments

  • Book1.xlsx
    10.6 KB · Views: 8
Last edited:
The result shows
A62C/R0/CAR/19/0001
A62C for year 19 and A63C for 20, RS become R0 trying to understand how your formula works.
 

Attachments

  • 1602241385185.png
    1602241385185.png
    2.1 KB · Views: 11
cyliyu

Helping

Using Bosco's solution changes this way

= "ABC/RS/CAR/"&TEXT(C6,"yy /")&TEXT(SUMPRODUCT(0+(YEAR(C$6:C6)=YEAR(C6))),"0000")

Decio
 
Last edited:
QUOTE="cyliyu, post: 269144, member: 24854"]
.......Revision : "A62C >> year 19 , A63C >> 20 and RS become R0"......
[/QUOTE]

In D6, formula copied down :

=TEXT(RIGHT(YEAR(C6),2)+43,"A00C\/R\0\/CAR\/")&TEXT(C6,"yy/")&TEXT(SUMPRODUCT(0+(YEAR(C$6:C6)=YEAR(C6))),"0000")

71534
 
Back
Top