Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Intermediary Links
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 -- Best Regards, FARAZ A. QURESHI |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Intermediary Connections | Excel Discussion (Misc queries) | |||
Locating Links and Removing | Links and Linking in Excel | |||
Removing External Links | Excel Worksheet Functions | |||
Removing links | Excel Worksheet Functions | |||
Removing links to other worksheets from within a workbook | Excel Discussion (Misc queries) |