Generate alphanumeric code based on date in cell

Hi all,

I have been working on a way to figure out if a request (one row) is approved, an alphanumeric code is generated based on the date that request was approved.

It should look like this "CR" + "YEARDATE-1" (so if I approve my first request in april 2017) the number generated should look like this CR201704-1. The last digit should be determined based on the already approved requests.

No I am stuck because the date is lined to the system date and the last digit is lined to the row, but this gives a false image since row 17 mag give "17" in the end whereas I only approved three requests you see.

This is the code I am using now:

=IF($R15="","",IF($R15="Rejected","",IF($R15="Approved","CR"&YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&"-"&TEXT(ROW()-1,"0000")-13)))

First row of the data is "15".

Thank you all for your help on this one! It's really appreciated.

Kind regards,

Bram