Auto paste special on lookup formulas if value is found

[Elgreco]

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

[SamT]

It sounds like:

• Exposure report is a database. Is it in a separate workbook?
• NAOPIP Review is a Report.

A Database is a permanent record of data that may be edited or appended, but is never deleted.

A Report is a temporary record of data the is to be viewed or printed, or possibly saved as a digital record elsewhere, but never saved in place.

There are several options to use. Personally, I prefer the UserForm option. It looks better and keeps Users away form Data.

1) UserForm Option: The placement of the I/O controls do not have to correspond to the columns on the Database or the Report. An "Invoice Number" ListBox or ComboBox can be used by the user to select the record they want to review or edit. Its contents can be filtered by various criteria, which can be selected with CheckBoxes. TextBoxes can be used to edit field data. CommandButtons can run various sub-routines (Marcos.) Entered or edited data can be validated. Only record of selected Invoice number is viewed. A Report sheet is not needed, so the Form can be stored in the Database workbook. A Report Sheet to be printed or saved can be created "on the fly," thus reducing file size.

2) Control buttons on the Report sheet Option: Code algorithms are more complex. ListBox Controls can be used to select various Sort and Filter sub-routines to run on the database. A GetData Command button can be used to run Filters and Sorts and Retrieve resultant Records. A SaveRecord(s) CommandButton can be used to save one selected record to the database (Easy) or multiple Records (Complex.) Sheet must be scrolled to view desired record. Report Sheet can be cleared on Save, thus reducing file size.

Both Options eliminate Formulas, thus shrinking file size tremendously.