Auto Cell Increment Contain Text and Number

[cyliyu]

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.

 

[bosco_yip]

Try,

In D6, formula copied down :

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

Regards

 

[cyliyu]

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.

 

[deciog]

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

 

[cyliyu]

Thanks. It works now.

 

[bosco_yip]

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")