Help with linked formula

[khaledocom]

Good morning all,

In the attached Excel file (Please open):

The red colored part is changeable in formula part. Instead of changing the link (file name) manually every line, Is there a formual, code or macro can do this for me. Especially there're 3224 rows of formula for same Please help

[RichardW]

Try this macro, or see attached file

Sub Macro3() 
    Dim FName As String, FPath As String, SheetName As String, Row As Long 
    Dim FEnd1 As String, FEnd2 As String, FEnd3 As String, FEnd4 As String 
    FPath = Sheet5.Range("A2") 'put your path in this cell - C:\Qtel\04895087\2010-April\QtelRecovery\Files\
    SheetName = Sheet5.Range("A3") 'put the sheet name in this cell - QtelRecovery
    FEnd1 = Sheet5.Range("A4") 'put the cell reference in this cell - $C$7
    FEnd2 = Sheet5.Range("B4") 'put the cell reference in this cell - $F$18
    FEnd3 = Sheet5.Range("C4") 'put the cell reference in this cell - $F$20
    FEnd4 = Sheet5.Range("D4") 'put the cell reference in this cell - $G$23
    Row = 6 'Start row of data
    Do While Worksheets("Sheet2").Cells(Row, 1) <> vbNullString 
        FName = Right(Cells(Row, 1), 22) ' assuming the file name will always contain 22 characters
        Cells(Row, 4).Formula = "='" & FPath & "[" & FName & "]" & SheetName & "'!" & FEnd1 
        Cells(Row, 5).Formula = "='" & FPath & "[" & FName & "]" & SheetName & "'!" & FEnd2 
        Cells(Row, 6).Formula = "='" & FPath & "[" & FName & "]" & SheetName & "'!" & FEnd3 
        Cells(Row, 7).Formula = "='" & FPath & "[" & FName & "]" & SheetName & "'!" & FEnd4 
        Row = Row + 1 
    Loop 
End Sub 

Regards
Richard

[khaledocom]

Thanks a lot my dear brother, I'll test it and let you know.
Good day to all.

[khaledocom]

I tried but it didn't work with my file,