Consolidate formulas pointing to other formulas into one

Hello,

Often time when I'm trying to build out a long formula, i'll break into components in different cells to make sure I see the values and decide what part needs tweaking. At the end, when all components look right, I want to take the final formula and combine all the pieces into it without have to copy/paste and re-drag the appropriate references. For example here's a date function I'm working on:

A1:6/6/2013 1:51:00 PM
A2:6/7/2013 8:26:00 AM
A3:0.20833
A4:0.66667

B1:=IF(A1-INT(A1)>A4,0,A4-(A1-INT(A1)))
B2:=IF(A2-INT(A2)<A3,0,(A2-INT(A2))-A3)
B3:=IF(INT(A1)=INT(A2),A2-A1,NETWORKDAYS(A1,A2)-2+B1+B2)

So in B3, I want to consolidate the references to B1 and B2 into the formula: =IF(INT(A1)=INT(A2),A2-A1,NETWORKDAYS(A1,A2)-2+IF(A1-INT(A1)>A4,0,A4-(A1-INT(A1)))+IF(A2-INT(A2)<A3,0,(A2-INT(A2))-A3)). This is a little easier of an example with only 1 chain, but I've had several chains pointing all over before.

Any ideas?

Not sure I follow, is there a question in there?

You posted an example that indeed produces the desired result.
What's the problem?

That's pretty much exactly the same method I use to develop long complicated formulas.

The question is, can I get the long formula result at the end automatically? without the copy/paste method?

Again, this is a toned down example, but imagine more references, more pieces. sometimes it can be a lot of copy/paste and more potential for accidental pasting in the wrong areas of the formula.

I don't think so.

I'm sure you could make a macro that could parse through all the dependent cells of an original formula, and replace the references with those formulas.
But I wouldn't trust the results.
You need a human eye to combine the formulas together into one.
Becuase sometimes you need to put () around parts to get the correct results.

example.

A2=1
A3=2
B2=3
B3=4

A1: =A2+A3
B1: =B2+B3
C1: =A1*B1 (Correct desired Result is 21)

But if you just do a straight copy paste of the formulas from A1 and B1 into C1, you get
=A2+A3*B2+B3 (incorrect result is 11)
Because it does the A3*B2 first, Then adds A2 and B3

To get the correct result you need to add () around the A1 and B1 formulas
=(A2+A3)*(B2+B3) (correct result of 21)


So you need a human eye to do the combining.


It's easiest to do the combining immediately after the formula is developed. While it's fresh in your mind.
Going back and doing it on something that's been in place for a while is difficult...but doable.