Auto Copy > Paste Values

Hiya,

Not sure if I am in the right sub-forum but I will give it a crack.

I do processing for an insurance company. When we process new clients, we have to allocate them an original policy number. We also have to provide them with a series of documents that contain information specific to the client (including the policy number).

To do this, we have two workbooks.
POLICY NUMBERS TO ALLOCATE.xlxs is used to find and allocate unused policy numbers. Column B contains the policy numbers, and column C is where you enter in the corresponding client name.
DOCUMENTATION ENTRY SHEET.xlxs is the template we use to produce the required documentation. We enter the required information into the first tab, then print PDFs of the other 5 sheets. The allocated policy number is entered into F8 on Sheet1. We save a completed workbook template under each client name so we can go back and edit/reproduce documentation when necessary.

Currently we are manually allocating the policy number in POLICY NUMBERS TO ALLOCATE.xlxs, then we are manually entering the number into DOCUMENTATION ENTRY SHEET.xlxs (F8).

In order to automatically find the next available policy number and copy it into F8, I have entered the function =INDEX('POLICY NUMBERS TO ALLOCATE'!$B:$B,COUNTA('POLICY NUMBERS TO ALLOCATE'!$C:$C)) into (F8) DOCUMENTATION ENTRY SHEET.xlxs. This is working, however it continues to update. If the next available number is 12345, then every individually saved completed template that contains the above formula will produce 12345 in F8.

IMO, the most basic solution to this is to manually Copy > Paste Values. However, having to remember to do this increases the margin for error and will most likely lead to multiple clients being allocated the same number.

Is there any way I can make it so F8 automatically Pastes Values? Or perhaps produce an error message if the user has not done so themselves?

Thank you in advance