Macro needed in a file with lookup formulas that will freeze formulas if value is found

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

Not sure what "Freeze" means. But you can use ISERROR() to detect whether a cell has an Error value (aka #N/A, DIV/0, etc...).
ie iserror(range("A1").value). A1 = #N/A then it will return TRUE, otherwise it will return FALSE.

Hi slx,

Thank you for the response. By "freeze" I mean paste special so the value remains.
The isserror formula will keep the links to the other file. That means that when the invoice is closed it will just return blanks.

I need to keep the value that the lookup will bring up. I believe that is done by pasting special on the line items that will find a match from the source file.

Hope that helps clarify the desired function.

Thank you

You want to replace an invalid answer with another answer. You can either do in the worksheet as a formula by:

=if(Iserror({Your formula that might generate an error}), {Error is True, use some other reference or default answer}, {Error is False, Put in the same formula to get the real answer})

Otherwise you need to use a looped if statement to do the same thing in VBA.

I was thinking it might be easier to paste special the formula while there is an answer.
For instance, there is a value in the lookup today. That value might be NA tomorrow. My thought was to paste special that value while it is available, today, before the user closes the file.
Can that be done?

The reason why the iserror can't work is because if the outcome is NA, the value that I would want to replace it with is gone forever.
So if the Customer name for invoice 1234 is Ford Motors, that information is available today and it will populate.
If Ford pays the invoice tomorrow and it is no longer in the source file, the output file will show NA. There will be nowhere to pull the fact that invoice was a Ford invoice. So when the lookup function would try to default to the "isserror" function, it wouldn't be able to pull that information from anywhere.

However, if, when Ford Motors populated today, we would want to keep that info for that invoice before we close the file.

Hope that makes sense.

Thanks

So is there a way to automate pasting special once values have been retrieved in a cell while leaving formulas in other lines below with no values still active?