Looking up a value in an Excel file in Dropbox.

[donf13]

Our company has one excel file in dropbox and another file on my computer looks up a value from a column in the dropbox file.  Others need to use the file on my computer, so is there a way to change the link so it will work for everyone?   current formula is VLOOKUP(A17,'C:\Users\myusername\Dropbox\foldernameindropbox\[filename.xlsx]worksheetname'!$G:$H,2,FALSE).

[Megan]

Hey @donf13, I hope you're doing well!

Just to make sure we're on the same page here: will the rest of the people be using a different user profile, or the same one? 

I'm assuming based on everything you mentioned, that they'd use different profiles, right? 

Let me know more, and we'll take it from there!

[Rich]

---

@donf13 wrote:

Others need to use the file on my computer, so is there a way to change the link so it will work for everyone?

---

As you've discovered, Excel uses full paths when linking or referencing another file, and will not work with a relative path or an environment variable (such as %USERPROFILE%). The only solution would be to relocate the Dropbox folder for each user so that it's in a common location, such as C:\Dropbox, so everyone is using the same full path.

You might be able to set a custom variable using VBA, and then use that variable in the lookup path, but I've never tried that. Try Google for more information on that.

[donf13]

each has their own user profile