Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Help with linked formula

  1. #1
    Join Date
    28th June 2010
    Posts
    13

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st December 2009
    Location
    Australind, WA
    Posts
    40

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th June 2010
    Posts
    13

    Re: Help with linked formula

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th June 2010
    Posts
    13

    Re: Help with linked formula

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Keep Track & Record Linked Formula Cells
    By base669 in forum EXCEL HELP
    Replies: 4
    Last Post: March 10th, 2008, 12:41
  2. File Path Changes On Linked Formula
    By pt5014 in forum EXCEL HELP
    Replies: 4
    Last Post: October 14th, 2006, 04:53
  3. Replies: 1
    Last Post: June 29th, 2006, 05:40
  4. External linked formula
    By diglife in forum EXCEL HELP
    Replies: 3
    Last Post: June 16th, 2004, 14:46

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno