I missed that you did want the formula for SequenceNo too!

I added a formula, but the problem with SequenceNo calculated using formula is, it will be different whenever you sort your table, or add a new entry using Insert Row.

When you want to avoid this problem, you need a hidden sheet with table where every column represents a year. The table has a single row of data, where for every year next available SequenceNo is calculated (Max SequenceNo for given year + 1 to counting part of it). I.e. when you have a column for 2017, and there is no approved transactions registered or SequenceNo is not determined for them, then the next available SequenceNo will be "2017-01". When you have registered SequenceNo "2018-08", then in column 2018 the next available sequenceNo will be "2018-09", etc.

Now you define a dynamic named range - e.g. lSequenceNo - which returns a cell from hidden table column matching with year of transaction approval date on active row in transactions table. And you use this named range as source for Data Validation List in SequenceNo column. Now you can assign an unique sequence number to every approved entry in transactions table simply selecting it from Data Validation List - there will be always a single choice. I leave it for you to try out as an exercise :-)

Attached Files

Transactions.xlsx (22.9 KB, 12 views)