Add 1 to existing reference number in table

K1600

Board Regular
Joined
Oct 20, 2017
Messages
178
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
980
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Could you please give an example?
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
178
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

Well-known Member
Joined
Nov 10, 2006
Messages
980
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
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?
 

Attachments

  • RefNo.JPG
    RefNo.JPG
    34.3 KB · Views: 4

K1600

Board Regular
Joined
Oct 20, 2017
Messages
178
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

Well-known Member
Joined
Nov 10, 2006
Messages
980
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
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?
Code Table.JPG
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
178
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

Well-known Member
Joined
Nov 10, 2006
Messages
980
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
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:
VBA Code:
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:
VBA Code:
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

B4 & After.JPG
 

Sahak

Well-known Member
Joined
Nov 10, 2006
Messages
980
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
You are welcome. I hope it will help, let me know if you have any questions
 

Forum statistics

Threads
1,175,545
Messages
5,898,059
Members
434,691
Latest member
2_nisia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top