Formulas using shared spreadsheet in DropBox

[StaceyVECL]

Hi everyone,

I have excel spreadsheets saved in a shared dropbox folder so that myself and colleagues can access and update. I have entered formulas to pull in information however its looking on my C drive which obviously returns an error when a colleague opens the spreadsheet.

Current formula using: =IF(B410="","",(VLOOKUP(@$B$398:$B$500,'C:\Users\User\DROPBOXFOLDER\[Shipment Details.xlsx]RM Formulas'!$E$1:$S$550,4,FALSE)))

The formula works for me but returns #N/A for colleagues. Is there a way to use Dropbox files without looking at the C drive? I have searched online and can't find anything useful.

Thanks in advance. Stacey.

 

[rlv01]

A similar problem that may help you.

Using filepaths in VBA code - files located on DropBox

Hello and thanks for considering an issue I'm bringing up. First off - this is relevant only to files stored on DropBox or, very likely, other cloud storage platforms. And only due to the files being accessed on different computers. The problem appears to be that Excel typically uses absolute...

www.mrexcel.com

 

[StaceyVECL]

A similar problem that may help you.

Using filepaths in VBA code - files located on DropBox

Hello and thanks for considering an issue I'm bringing up. First off - this is relevant only to files stored on DropBox or, very likely, other cloud storage platforms. And only due to the files being accessed on different computers. The problem appears to be that Excel typically uses absolute...

www.mrexcel.com

Thank you for the reply @rlv01 - this hasn't worked and now returns a #NAME error for myself. I did notice that excel has updated the formula so that @ is before filepath and environ.

Formula now looks like the below.

=IF(B413="","",(VLOOKUP(@$B$398:$B$500,@filepath="'C:\Users\" &@Environ("username") & "\VECL Dropbox\[Shipment Details.xlsx]RM Formulas!$E$1:$S$550",4,FALSE)))

Any other suggestions?