Add 1 to existing reference number in table

[K1600]

I have a table with a varying number of rows depending on the current data set within it. I then look through the rows and add either 'approve' or 'decline' to column C (called "Status" in my table). If the row is 'Approved" then it needs a unique reference number which is made up as follow 'XXX/22/1' with the "1" being a sequential number. Currently I have to add the sequential number manually into column A having checked back through my previous approvals to find the latest number.

Is there a way I can get my refence number which is in column B (named "Ref No" in table) to be generated automatically if "Approved" is added to column C with the next available number but sticking with my existing reference number format of three letters, "/", two number (current year), "/" and then the unique number?

I should add, that the rows may not necessarily be 'approved' or 'declined' in order of row so rows 20-30 may be approved today and will get a reference number but next week rows 3&4 might then be 'approved' so I need the next sequential number in the whole column and not simply an increase of one from the last cell with an entry.

Thanks

 

[Sahak]

Could you please give an example?

 

[K1600]

Could you please give an example?

Sorry for the delay, I missed the e-mail notification that you had responded.

I've I've done it correctly I have attached a link having put the file in DropBox then created this share link.

It is a sample table I have mocked up to show the basics of what I am trying to do but in essence if I put 'Approved' in the 'Status' column then I want it to look for the highest reference currently in the 'Ref No' column and then add a 1 to it meaning that I can then get rid of the 'Unique No' column. In the example in the link, If I were to approve any of the other rows in the table then it would get the reference of XXX/22/4.

Hope this makes sense.

 

[Sahak]

Thank you for your reply,

I understand that if we put “Approved” in column “Status”, in column “Ref No” automatically should be created reference No, which on the right side should have “/” then two numbers (current year), then “/”, then plus 1 to an existing highest number which we have at the end of Ref No, in your sample it is in row 10 & it is the number 3. But I don’t understand to what (in your sample you show “XXX”, from where we should get that Xes) to add them?

Let say on row 8, where we have “Katie” only, we add “Approved” in column “Status”, what we should have before /22/4?

 

[K1600]

Thank you for your reply,

I understand that if we put “Approved” in column “Status”, in column “Ref No” automatically should be created reference No, which on the right side should have “/” then two numbers (current year), then “/”, then plus 1 to an existing highest number which we have at the end of Ref No, in your sample it is in row 10 & it is the number 3. But I don’t understand to what (in your sample you show “XXX”, from where we should get that Xes) to add them?

Let say on row 8, where we have “Katie” only, we add “Approved” in column “Status”, what we should have before /22/4?

Yeah that's right, the xxx is a three letter reference, that will never change within the table so can be built into the formula. I have a few of these tables and the three letters are different on each so in the table where the letters are sat, ABC the reference for route 8 for Katie would be ABC/22/4.

Hope that makes sense.

 

[Sahak]

One more thing
So, we have a table & each name in the table must have different three letters, Kate, for example, has the three-letter code ABC, Fred has for example MBF. Those three letters should automatically come from the table like below, so, on row 3 automatically we should get MBF/21/5 correct?

 

[K1600]

One more thing
So, we have a table & each name in the table must have different three letters, Kate, for example, has the three-letter code ABC, Fred has for example MBF. Those three letters should automatically come from the table like below, so, on row 3 automatically we should get MBF/21/5 correct?
View attachment 53351

No, every reference in this table will start with the same the letters then / followed by 22 then / then the ascending number. Once set in the table, the only thing that will change within that table throughout the year is the ascending numbers at the end.

 

[Sahak]

I did not completely understand what are you looking for, but I think these codes will be helpful.
Below see 2 versions of VBA codes.
Version 1:

Sub Enter_Ref_No_V1()
    Dim RefNo As Range
    For Each RefNo In Range("A2:A10")
        If RefNo = "" And RefNo.Offset(0, 1) = "Approved" Then
            RefNo.Value = "XXX/" & Format(Date, "yy") & "/" & Range("F1").Value + 1
            Range("F1").Value = Range("F1").Value + 1
        End If
    Next
End Sub

This will work if you will keep maximum number in any cell (In my example it is “F1”). You need to put there the maximum number one time only, future counting will be done automatically. If you like you can hide that cell, it’s up to you.

Version 2:

Sub Enter_Ref_No_V2()
    Dim RefNo As Range
    For Each RefNo In Range("A2:A10")
        Dim n As Long
        n = Application.WorksheetFunction.CountA(Columns("A:A")) - 1
        If RefNo = "" And RefNo.Offset(0, 1) = "Approved" Then
            RefNo.Value = "XXX/" & Format(Date, "yy") & "/" & n + 1
        End If
    Next
End Sub

In this code, the maximum number will be assigned automatically according to nonblank cells in column A.

Attached please see the table before & after running VBA code

 

[K1600]

Great thanks, I shall give it a try later.

 

[Sahak]

You are welcome. I hope it will help, let me know if you have any questions