Inherited Complex Formula, Need Assistance to Change

Greetings to all,

I have an inherited formula:

Please Login or Register  to view this content.

That is rather complex, but does what it is supposed to do.

It is supposed to work whenever we get raw data imported to a worksheet (137S in this particular case) in an existing workbook. Then we compare the first column in the raw data (which contains the id for all the data) to the second column of data in a worksheet that contains data we have previously received (SUPPLEMENTAL(BTO)), to see if there is any new data in the raw data (137S) that needs to be added to the already received data (SUPPLEMENTAL(BTO)).

Like I said, it works, but when the new data (from 137S in this case) is added to the already received data, the contents of the cell with the formula go from showing the ID (it is a WBS (work breakdown structure) number, i.e. S.0000021.1.1) for the new data to blank, which is understandable because the comparison between the raw data and already received data matches after the new data is added to the already received data.

What I would like to do is this, once data is identified as new data, store the new data ID (WBS Number) somewhere (new worksheet?) before the new data is added to the old data, so it is viewable after adding the new data to older data. But this formula is so convoluted I am having a difficult time determining where/how to accomplish what I am trying to accomplish. I wish I could store it on the same worksheet that contains the formulas for identifying new data, but since this is just one instance of seeking new data and there are seventeen different worksheets with raw data. All the formulas for checking for new data are on a single worksheet.

I have attached a screenshot of the worksheet that contains the formula above so you can see what I am talking about when I say there is no place to store the new data ID once found.

I'd even overwrite the cell the new id is in with the ID if i could, but like I said, don't know where to put that in the rather complex formula above.

Any and all assistance is greatly appreciated .