Search code examples
excelvba

How to allocate used serials to new entries in a document tracking system?

I have a sheet that contains the details for cheques received from our customers that get deposited as and when due. Currently, we follow a filing system where each cheque is assigned a number to track and retrieve which are serial. However, the serial are now running into 10,000 but I only have a few hundred cheques in possession.

How can I automate the numbering of the cheques that I receive to exclude the cheques that have been deposited / cleared based on a helper column that contains the status of the cheques as either "Cleared", "Uncleared" or "Deposited".

  1. Column A - Date of Receipt
  2. Column B - Customers Detials
  3. Column C - Cheque Numeber
  4. Column D - Cheque Date
  5. Column E - Remarks
  6. Column F - Status (all pending cheques are tagged "Uncleared", cheques out for collection are "Deposited" & cheques credited to the account "Cleared")
  7. Column G - Filing number

I'm open to VBA scripts as well if I can automate it.

Solution

  • Seems to me the easiest solution is to take the MAX of the known filing numbers and add one.

    So if you are entering these all into the same spreadsheet, and the spreadsheet has one header row, and the filing numbers are held in column G, you just need a formula like this in cell G3:

    = MAX(G$2:G2) + 1

    and then fill it down so the cells below are

    = MAX(G$2:G3) + 1
= MAX(G$2:G4) + 1
= MAX(G$2:G5) + 1
= MAX(G$2:G6) + 1

    so that it is taking the MAX of all the filing numbers immediately above and adds one.