Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Help with linked formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with linked formula

    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
    Attached Files

  • #2
    Re: Help with linked formula

    Try this macro, or see attached file


    Code:
    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
    Attached Files

    Comment


    • #3
      Re: Help with linked formula

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

      Comment


      • #4
        Re: Help with linked formula

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

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X