• 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

Last edited:

deciog

Active Member
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:

bosco_yip

Excel Ninja
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
 
Top