|Hi, thanks for the help.|
I thought I attached a print screen of how my workings worksheet looks like, but apparently it failed to attach to this article.
1) I had set it to refer to the Department Code in C7 where I will key in the 3 letter alphabet since we plan to give each dept one file each.
2) The last 3 chars just need to keep increase by 1 actually since each dept will have their own file. The number will be reset after a year.
3) C14:D25 is a table referencing to different period of the financial year since we don't follow calendar month, the first period 01 starts from Apr actually. So I used a lookup function.
But my problem was solved.
Recently I managed to find an article to add one line to my vba code to increment the value by 1 each time it runs.
Range("D10").Value = Range("D10").Value + 1
And another article that solved the problem to keep the reference number in 3 digits.
In cell C10 I input this formula
Might not be the best way, but it solves my problem using the simplest functions.
In the end my [Workings] worksheet looks something like this: