I need some help with the approach to a specific project. We have a large excel workbook with a ton of worksheets. There are many user inputs, which formula fields pull, return, repeat. This all ends up in a final report. Each worksheet has essentially become its own Model, View and Controller :).
I want to find a way to split the "model" and "controller" - basically to create a base data dictionary and a rules engine.
Here is what I think my strategy should be:
- Crawl through the workbook looking up every formula field.
- Record the results, which kind of becomes the rules engine list.
- Look at all of the rules generated and any input cell that isn't itself a formula field is likely a raw input field. Have these form the data dictionary.
The above approach is quite flawed - there are raw data fields that have "default values" which are generated via formula. Furthermore, there are formulas that just "fix" fields, like phone numbers. These aren't really "rules".
Before I just jump into the project, I thought I would ask if anyone has done this before, trying to reverse engineer a large workbook into a model and controller in order to create an application? Perhaps you have some insight that I am lacking (Excel is NOT my domain) or perhaps a better strategy?