*Automatically Resetting the Value of Invoice no to 1 whenever new month starts and updating the customer ID as per new invoice no.*

[MUSEB]

*different cell and it's data*

"A2" is Date in "dd-mm-yyyy"
"B2" is Year - *Year("A2")*
C2 is last two digits of the year "20" - *Right("B2",2)*
D2 is my month "04" - *Month("A2")*
and
E2 cell is my invoice no.
F2 cell is my customer ID.

Range("E2").value = Range("C2"). text & Range("D2").text & Range("E2"). text


Range("F2").value = "C" & Range("E2"). value


Suppose,
I am working for January.
then I need my invoice Number to increase by 1 for each new invoice.

after that if the month changes to February
then again my invoice Number should start from 1 and get increase by 1 for each new invoice.

It should follow the same every time the month get's changed.


*Example*
If A2 is 05-02-2020

then F2 should be C200201 for first invoice in that month.
for second invoice C200202
and
if the month changes to say March then first
value for F2 should be C200301.


Now as value of cell "D2" is dependent upon cell "A2".
*I need to run a macro whenever the value of D2 changes.*

*I am only be changing the date, I don't want to change the value of cell "D2" manually.*
I am having issues with automatic invoice no update, i.e, cell E2 value.


*When the value of A2 get changed, the macro should run automatically. This macro will contain the set of codes for incrementing the value of cell "E2" by 1, which in turn will update the value of customer ID i.e, cell "F2" automatically*

what is the possible vba code for this.
please reply...
Hope you got this.
Thankyou.

 

[jimrward]

Try in E2
=C2&D2&TEXT(COUNTIF($D$2:D2,D2),”00”)

note the double quotes above are from an iPad so you will need to replace them with double quotes from your keyboard