In Excel I have a cell containing a formula referencing other cells, which reference other cells and so on... forming a large tree of references. I'd like to substitute recursively all the occurrences of all the references so that the formula will contain only constants and no references to other cells.
E.g. Supposing I have these cells:
A1=3 A2=4 A3=5 A4=A1+A2 A5=A4/A3+1
is there a way to expand the formula of A5 into something like "(3+4)/5+1"?
I found the Precedents property in VBA which returns the list of precedents (the cells referenced in the formula at the various levels of the whole tree of dependencies). The solution would be to find their occurrence in the formula and substitute them recursively with the formula of the corresponding cells. However doing this syntactically is hard (e.g. think of substituting the occurrence of "A1" in the formula "=A10+A11").
I've been asked why I need this so here's my problem: My spreadsheet has a lot of financial calculations that I don't understand, with partial results which are spread over various cells. I'm interested only in the final result which is in only one cell and depends on a number of initial variables. If I had to trace all the dependencies it would be a long work. I need to work with different scenarios with different initial values and of course the final result. These scenarios will be set automatically by the solver (so I need to have them all in the same spreadsheet) in order to optimize a function.