Removing Intermediary Links

[FARAZ QURESHI]

An "EXAMPLE" is:

1. Sheet5!J1 = ROUND(Sheet4!I6,0)
2. Sheet4!I6 = Sheet3!B3-5
3. Sheet3!B3 = Sheet2!C5*70%
4. Sheet2!C5 = Sheet1!A1+4
5. Sheet1!A1 = Sheet1!C10
6. Sheet1!C10 = 6+10

Could anyone devise a code which would check:

1. If the cell referred to in a formula contains a formula again referring
to any other cell;

2. If NO, let it remain the same;

3. If YES, convert the cell reference in the current cell's formula to the
preceding cell;
4. Looping and carrying out such an exercise again and again until every
cell containing a formula links directly to the primary source.

Thus, in the example given above, with the Sheet1!C10 containing no formula
but a manually entered figure (like 16) or a simple formula which doesnot
involve anyother cell (like =6+10):

First the cell formulas would be converted to:

1. Sheet5!J1 = ROUND((Sheet3!B3-5),0)
2. Sheet4!I6 = (Sheet2!C5*70%)-5
3. Sheet3!B3 = (Sheet1!A1+4)*70%
4. Sheet2!C5 = (Sheet1!C10)+4
5. Sheet1!A1 = Sheet1!C10
6. Sheet1!C10 = 6+10

Then...

1. Sheet5!J1 = ROUND((((Sheet1!A1+4)*70%)-5),0)
2. Sheet4!I6 = (((Sheet1!C10)+4)*70%)-5
3. Sheet3!B3 = ((Sheet1!C10)+4)*70%
4. Sheet2!C5 = (Sheet1!C10)+4
5. Sheet1!A1 = Sheet1!C10
6. Sheet1!C10 = 6+10

Then...

1. Sheet5!J1 = ROUND(((((Sheet1!C10)+4)*70%)-5),0)
2. Sheet4!I6 = (((Sheet1!C10)+4)*70%)-5
3. Sheet3!B3 = ((Sheet1!C10)+4)*70%
4. Sheet2!C5 = (Sheet1!C10)+4
5. Sheet1!A1 = Sheet1!C10
6. Sheet1!C10 = 6+10

And Finally...

1. Sheet5!J1 = ROUND(((Sheet1!C10)+4)*70%,0)
2. Sheet4!I6 = (((Sheet1!C10)+4)*70%)-5
3. Sheet3!B3 = ((Sheet1!C10)+4)*70%
4. Sheet2!C5 = (Sheet1!C10)+4
5. Sheet1!A1 = Sheet1!C10
6. Sheet1!C10 = 6+10

Because Sheet1!C10 doesnot refer to anyother Cell