3

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.

Improve this question
3
  • 1
    Could you please flesh out your question with a bit more info on what you're trying to do and why? Right now it's really unclear (to me at least)
    – CLockeWork
    Jun 27, 2014 at 12:36
  • In any case, default formulae cannot look into the contents of another cell to see whether it is a formula or not, they only look at the result value.
    – Jerry
    Jun 27, 2014 at 13:19
  • Since the solution appears to be possible only by coding, maybe is it better I repost the question in Stackoverflow?
    – user339194
    Jul 2, 2014 at 14:20

1 Answer 1

Reset to default
0

You can use Replace (from the Home ribbon, choose Find & Select / Replace).

For your example, I would set up the Replace window as:

Find what: A4

Replace with: (A1+A2)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .