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