I’m in the process of automating my project cost reports that I have to produce every month. As a part of this automation, I need my Cost Report spreadsheet for each new month to run an automated check against every new month’s SAP Invoice Report that I’m just paste into another sheet tab. So, for every new month (as seen between the Invoice Report for May 2014 and June 2014 in cells L6 and L17), there is the possibility for a new Service Order Number and along with it a new Service Order Value (budget) and Invoiced Actual Cost that can pop-up in the SAP Invoice Report, but it may not be in my existing Cost Report. With all the projects I maintain, I run a high risk of not noticing a new service order and as a result may not add it into my monthly Cost Report.

What I need is a formula to do this automatically for me by recognizing the change between what is already there in the previous Service Order Numbers, locate the new Service Order Number from the new month’s SAP Invoice Report, and from that report automatically add itself and its related cost information to my Cost Report (as seen between the Cost Report for May 2014 and June 2014 in cells D7:H7 and D17:H17). And it would be the same for the next month and the next (rows D18:H18, D19:H19, etc.) as this formulas would be hidden hidden in my Cost Report awaiting for any new appearance of a Service Order Number.

Can somebody please help me on this one?

Please let me know if you have any questions or if I can provide any additional information to help in figuring out this formula.

Example.xlsx

Thank you,

Garrett