Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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


    VB:
    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