I apologize if this has been addressed and solved. I have been searching the interwebs for hours and while I can find my question many times over, I can't find a solution.
My company uses Dropbox and every user has his/her own Dropbox account, although they all point to the same shared folders. I have a vlookup formula in one workbook (called StartSchedule) that links to data in another workbook (called ActiveJobs). Since I wrote the formula, it works fine for me (let's say my user name is Jess). But when my colleague (let's call her "Sam") opens the workbook, the links cannot update as the links are looking for my specific file path (eg, 'C:\Users\jess\Dropbox\Company\Costs\[ActiveJobs.xlsx]Master'!$A$1:$E$100).
Is there anyway to have a vlookup formula that can dynamically reference the user's filepath where ActiveJobs is stored? I tried messing around with the Indirect function but I need a solution that doesn't require the target file to be open, which Indirect does.
I am not well versed in VBA (which is why I was hoping for a formula solution), but willing to try if it's the only way to do this (and of course if someone is willing to explain how to write the VBA code). Thanks in advance for any help.
My company uses Dropbox and every user has his/her own Dropbox account, although they all point to the same shared folders. I have a vlookup formula in one workbook (called StartSchedule) that links to data in another workbook (called ActiveJobs). Since I wrote the formula, it works fine for me (let's say my user name is Jess). But when my colleague (let's call her "Sam") opens the workbook, the links cannot update as the links are looking for my specific file path (eg, 'C:\Users\jess\Dropbox\Company\Costs\[ActiveJobs.xlsx]Master'!$A$1:$E$100).
Is there anyway to have a vlookup formula that can dynamically reference the user's filepath where ActiveJobs is stored? I tried messing around with the Indirect function but I need a solution that doesn't require the target file to be open, which Indirect does.
I am not well versed in VBA (which is why I was hoping for a formula solution), but willing to try if it's the only way to do this (and of course if someone is willing to explain how to write the VBA code). Thanks in advance for any help.
