I have 4 different workbooks. One is called ‘Universal Pricing Sheet’ and contains a sheet called S1Pri which has commodity price data. The other 3 workbooks also contain S1Pri sheets (with the same data). Additionally, these 3 workbooks all have sheets called ‘Output’ which contain revenue information based on the data in the ‘Output’ sheets. The results on the ‘Output’ tab are driven by and dependent on the S1Pri tab in each of the respective files.
I need to integrate 3 sheets called ‘Output’ (all of which are contained in 3 different workbooks) and cash flow models within the workbooks into an SQL database or storage module (could be Excel, Access etc). The goal is to link these three output sheets in a manner such that the data in each output sheet is driven by one S1Pri sheet – either through the ‘Universal Pricing Sheet’ excel file, or a copy of such a file in the chosen storage module. So when inputs to ‘Universal Pricing Sheet’ are made the contents of each ‘Output’ tab should be modified as they would if I went directly into the Excel file and changed the same thing on each of the individual S1Pri sheets. However the underlying 3 excel files should never be modified in any way. I want to replicate these output tabs, in an efficient manner, without changing the underlying files.
Also, I want it to be possible to implement process on a large scale with many input files rather than simply creating 3 excel copies of the files and manually link the inputs of each file to the Universal Pricing Sheet.
I would just like some suggestions on how to approach this problem.