You might see that the Dropbox Community team have been busy working on some major updates to the Community itself! So, here is some info on what’s changed, what’s staying the same and what you can expect from the Dropbox Community overall.
Forum Discussion
Excel formulas not showing correctly on share account version of file
I inherited ownership of a shared spreadsheet from a retiring colleague. I moved the 'original' spreadsheet to my own (local) Dropbox folder on my machine. I have subsequently edited the spreadsheet to include additional formulas and computations. Specifically, I have add the following formula in several cells:
=RIGHT(CELL("Filename",I1),LEN(CELL("Filename",I1))-FIND("]",CELL("Filename",I1))) & "-" &COLUMN()-5
This works as expected on my own machine, but I am being told that on all of my colleagues machines, when they open the file from their Dropbox folder locations (which should be synced) they are getting an error. I haven't confirmed the specific details yet, so I don't know whether it is a #REF! error or a #VALUE! error or a #NAME! error, etc. But I'm trying to troubleshoot. Are there any known Excel formulas or techniques that cannot be correctly used within a shared Dropbox file? If so, where can I find a list. If not, how can I go about troubleshooting / resolving this issue? Dropbox won't help much if I make formula changes but nobody else can see them!
EWeberMU wrote:
I am not using a reference to the file's name on my own local computer. As a result, when the file gets synced to each shared Dropbox user's file, this "filename" reference should now reference whatever their file name is (including path location, etc.) Unless there is some literal translation that is getting embedded within the CELL function that I can't control, this shouldn't be an issue. What do you think?
I was originally thinking that you replaced the path to the file with the word "filename" in an attempt to expunge the actual name of the file. That being said, if filename is truly nothing more than a reference to itself, and Office doesn't try to replace that with the full path, then it should be fine. If Office is replacing filename and instead embedding the full path and saving it within the file, it won't work.
Either way, Dropbox isn't the cause. It simply can't change your file in that manner. All Dropbox does is move files around. There's not even anything special about the Dropbox folder. It's just a folder like any other on the computer.
Have them drag the file out of the Dropbox folder on their computer and then open it. What happens?
I have discovered the details about this situation that points to a new problem. The problem is that when some of my colleagues received an Email notice from Dropbox that the Excel spreadsheet has been updated, if those users do not have the Dropbox app installed on their local machines, but DO have an Office 365 account, then the web link in the Email opens the Excel file in a cloud-based location presumably instead of using their local installation of Excel and the synced version of the file. As a result of this web-based Excel program, it cannot resolve the file name properly. A work-around solution was to have my colleagues install the Dropbox app on their local machines. That way, when the open the file that way, then Excel is working with their local, synced file. The larger problem that this points to is that any document that expects to work with 'file information' when the physical file is being processed in a dynamic (think SaaS or cloud) implementation may discover that the non-local program may not have the ability to access information about the non-local file properties. With more and more people and organizations moving to SaaS programs, this may be something that needs to be considered.
Update to my question below:
I found my solution here:THank you
- Rich
Super User II
Dropbox does not alter your files so it likely has nothing to do with the error. All Dropbox does is sync the file from one computer to another. The file is the same on both ends.
You appear to be referencing an external file in the formula. Office usually uses the full path to a file, and the full path to a file stored in Dropbox will be different from one computer to another. Meaning, it will work on the computer that created it, but on another computer the formula is looking for a file that does not exist.Hey Rich,
Thanks for replying but I don't think your response is correct. I am using the CELL function with a "filename" parameter. The actual parameter is the literal keyword "filename" in quotes. I am not using a reference to the file's name on my own local computer. As a result, when the file gets synced to each shared Dropbox user's file, this "filename" reference should now reference whatever their file name is (including path location, etc.) Unless there is some literal translation that is getting embedded within the CELL function that I can't control, this shouldn't be an issue. What do you think?
- MarkGoing to be honest and say I'm not 100% sure around the 'filename' command - google is best bet for that, however, Rich's response around Dropbox not changing anything is spot on. So somewhere along the line the formulae isnt working between the machines and isnt playing well with things.
You may get more specific advice and help on the Microsoft forums
@EWeberMU - did you find a solution to your issue?
I am having the similar problem.When i open the fiel from my Mac's DB finder folder my vlookup formula doesnt execute properly for example:
=VLOOKUP($B$2,'Sales summary'!$B4:$W20,2,0)
What happens next is the above formula shows up in cell B2 of the refernced sheet where the table array is instead of populating the data to the cell in sheet 1 .
However, when i open the same file (copied from the DB finder folder to my local desktop) - the formula executes without an issue.
My antennaes are pointing towards DB being the issue but dont knwo where to go from here.
Any help/steer in the right direction is appreciated.
