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.
To be frank, what you are doing is an accident waiting to happen. So many problems and mistakes can happen when you are linking spreadsheets and doing calculations within linked spreadsheets.
Keep your prices in a database. Obviously date is the primary key. Interact with a stat package like R. The advantage of this is the functions are hard coded and as long as you code correctly, you will not make mistakes.
This is the correct and safe way to do this but I understand this involves tech skills which most finl people don’t have.
You can set up a data connection within the workbooks to query a database, external workbook, or text file. It’s pretty easy to do, just Google “Excel external data”. The easiest thing to do would be to set up the connection to load the data to the same location where it resides now, which would leave all of your formulas intact. Make sure you check the box to refresh on open and edit the workbook properties in the VBE to update all links.
BostonGeorge And Blake are both correct, I have created similar models before pulling data from a single excel ‘database’ workbook into multiple other wokbooks but it presented with a few problems
-one the computing power needed to refresh and update the worksheets became enormous, which eventually led to lock ups and crashes even with an upgraded p7 processor.
-enormous issues with data integrity in terms of updating the core database and keeping track of what information was being pulled into my models and pivot tables.
- it just became a headache to fool around with all the time and I became worried that inconsistencies in the data would arise which would render my reports inaccurate/useless.
It was a band aid work around that was actually less complicated then what you are describing yet it was a finicky process that wasnt very confidence inspiring.