Hello

I am stuck with an assignment that I have spent a lot of time trying to come up with a conventional solution and I am at a point where I need advise from the esteemed sages on this forum.

Output File name: "NAOPIP Review" with multiple line items (~2k).
Instructions: The tab has headers from column A to column AJ. User manually inputs values in column F (invoice numbers, all numeric). There are lookup formulas that reference the invoice numbers from another file called "Exposure report" and bring information related to those invoices in each line in the output file (NAOPIP Review) across all the columns from A to AJ, skipping column F which contains the invoice number and is manually input.
The user will be going back into the NAOPIP Review file adding more invoices on a daily basis so the file will need to have to have the live lookup formulas in place that will bring up data as matches are found from the source file (Exposure report).

The problem is that, as time goes by, once the invoices are closed on the source file (Exposure report) the invoices will drop off the list on that file and the lookup formulas in the NAOPIP Review will go to #N/A. We don't want that to happen, we need to freeze that info once it is populated for reporting reasons.

GOAL:
When information on the invoice is pulled up from the Exposure report, the line has to paste special those values and eliminate the formulas, freezing that information.
Whatever solution we come up with will have to keep all the lookups active in that file if the invoice column (F) is blank, so we can't freeze the whole range, only when values are found on Exposure report.
The macro can either be activated on file closure or, more preferably with a button that I can place on the NAOPIP Review file that a user can click on once the input has finished. This way they can keep the file open for review or confirmation that all work was done accurately.

I thank in advance for any assistance on this.

Thank you

Vassili