Linking Worksheets when paths change

[debbiefoster]

Hello,

I have spent some time googling and searching in this forum for this answer, and I am sure it exists somewhere, but I am not clear on it, so I apologize in advance if this is a newbie question.

I have a folder in Dropbox (important that it is stored there, it is shared with 15 people who are on a bunch of different networks) that contains 15 spreadsheets. One of them is the master, containing performance data for 14 people. The other 14 are for each individual person pulling their performance data from the master. Monthly, the master spreadsheet gets updated, and I want the user spreadsheets to update, so I can email the individual spreadsheets to each person so they can track their performance. Of course, they cannot see each others numbers, so they have no access to the folder. There are a handful of people that have access to the master. All the spreadsheets will live in this folder all the time.

The issue is that on my computer the path to the master is different then it is for other users because of where Dropbox stores local data by default, and for a lot of reasons, I cannot change it.

Is there any way to tell excel that the master (or the other 14) live in the same folder so there is no need for the first part of the path? Or, any other suggestions?

Thank you in advance!

Debbie

 

[cstimart]

I'm confused, can you give example(s)?

 

[debbiefoster]

One folder, on my computer path is C:\Users\Debbie\Dropbox\Commissions

In that folder is an xls file called Master Commissions.xls where all data is entered, for all 14 consultants.

In that folder is an xls file called Alex, and that spreadsheet pulls his performance numbers from the master commissions.xls. When I paste the link, it is looking for the data at the path above.

When someone else opens the Alex spreadsheet, on their computer, they do not have C:\Users\Debbie\Dropbox\Commissions - on their computer, the path is C:\Users\John\Dropbox\Commissions.

So, I want to know if there is a way to tell Excel to just look in whatever folder the spreadsheet was opened from, and find the FILE (not path) called master commissions.xls.

Sorry for the confusion.

 

[cstimart]

Are you using VBA? If so, the following Function will return the user name (i.e. Debbie, John, etc...)

Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
 
Function NTUserName() As String
    Dim rString As String * 255
    Dim sLen As Long
    Dim tString As String
    
    tString = vbNullString
    On Error Resume Next
        sLen = GetComputerName(rString, 255)
        sLen = InStr(1, rString, Chr(0))
        If sLen > 0 Then
            tString = Left(rString, sLen - 1)
        Else
            tString = rString
        End If
    On Error GoTo 0
    NTUserName = UCase(Trim(tString))
End Function

 

[debbiefoster]

No, unfortunately VB is way above my head. I was hoping their was a simpler way to do it.

 

[cstimart]

No, unfortunately VB is way above my head. I was hoping their was a simpler way to do it.

Use Alt-F11 to open
Insert Module
Paste all the code in


Then on your worksheet, "=NTUsername()" will return "Debbie" or "John", etc...

You can then utilize that in your worksheet.

 

[debbiefoster]

Great! Thanks - I will give that a shot.