Sheet Referencing

Good afternoon,
Ive got one of our users who want to update values from another sheet, the issue is the other sheet in in another folder.

I know to reference another Excel document I can simply use spicify the complete folder and then the sheet, but if I have my workbook in one folder up and then into another, how can I reference that?

I would have thought I could use "'..\My Second folder\[myReferencedWorkbook.xls]Sheet1'!$C$9" (go up from my current folder and then into 'My Second folder', but it doesnt seem to work.

So for example:-
Test
+---My First folder
¦        My First Workbook.xls
+---My Second folder
           myReferencedWorkbook.xls


Is is possible to do referencing like this, or do I need to use the full path when moving up and down folder paths?

Thank you
tonelm54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieData ProcessorCommented:
Sorry, you need to use the full path.
0
Frank WhiteCommented:
Unfortunately, MS Excel doesn't allow relative path referencing. See this old discussion for more detailed explanations.

If you're familiar with Excel Macros and VBA, you could use a VBA routine to get the path of the current worksheet, parse up one level, and append the relative path. Then you could use the Workbook object's .LinkSources and .ChangeLink methods, if necessary, to do the updating easily. It would also be possible, though more clumsily (and usually much slower) to parse the filepath fully and then compose a new formula to inject into worksheet cells, if updating links directly after the workbook has already been opened (and security settings and user intervention possibly having already messed with the links) is not a reliable option.
0
dlmilleCommented:
Here's a sub I use when I need to do this and its tedious doing it manually (re: i have more than one link, or I keep getting the pop-up to select the source).  I made a few modifications to get you started with your references:
Sub changeAllLinks()
Dim aLinks As Variant
Dim oldLink As String
Dim newLink As String
Dim oldName As String
Dim newName As String
Dim i As Long
Dim myWkb As Workbook
Dim startTime As Long
Dim endTime As Long

    startTime = Timer
    Application.DisplayAlerts = False
    Set myWkb = ThisWorkbook
    
    aLinks = myWkb.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            If aLinks(i) Like "*myReferencedWorkbook.xls*" Then 'modify this to find the old references
                oldLink = aLinks(i)
                oldName = getFileName(oldLink)
                newLink = onePathUp(ActiveWorkbook.Path) & "\mySecondFolder\" & oldName 'modify this to change the folder location
                If oldLink <> newLink Then
                    myWkb.changeLink Name:=oldLink, newName:=newLink
                End If
            End If
        Next i
    End If
        
    Application.DisplayAlerts = True
    
    endTime = Timer
    MsgBox "Process Complete in: " & Format((endTime - startTime) / 60, "#.00") & " Minutes"
End Sub
Function onePathUp(strPath As String) As String
    onePathUp = Left(strPath, Len(strPath) - InStr(StrReverse(strPath), "\"))
End Function
Public Function getFileName(strPath As String) As String
Dim i As Integer
    i = InStr(StrReverse(strPath), "\")
    getFileName = StrReverse(Left(StrReverse(strPath), i - 1))
End Function

Open in new window


Cheers,

Dave
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.