So here is what I am currently doing, but would like to simplify it with a formula if possible.
I have an excel file that is converted from a PDF. For this example the file name is '001.XML'
I have another file named '10pcs_123_JULY2021.XML'. This file has three tabs, FORMULAS, SN, DATA ONLY. The DATE ONLY tab has charts that compile the data and rules to look for values greater or less than specific numbers. (This file is submitted to my customer.)
Right now I take a list of Serial Numbers and enter them into to SN tab and sort by low to high. In the FORMULAS certain cells copy the serial numbers from the SN tab and other cells have a formula to retrieve values from another excel file when that file is open. Once this file (example, excel file '001.XML') is open the values pop up into specific cells in under the FORMULAS tab in the '10pcs_123_JULY2021.XML' file. I then copy those cells, go to the DATA ONLY tab and paste the values. Once the '001.XML' file is closed the values from the FORMULA tab say #REF! and the values in the DATA ONLY tab remain. This is exactly what I want.
What I would like to is a code and/or shortcut that will 'lock' those values into the DATA ONLY tab, so I can eliminate the copy and paste values step. Any ideas?
Thanks in advance,
Marc
Bookmarks