Want to know more about Dash? Check out how Amy uses Dropbox and Dash to make her day easier here!
Forum Discussion
Solved
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
9 Replies
Replies have been turned off for this discussion
- 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 Hey Mark,
Thanks for the follow-up and I do agree with both your's and Rich's responses... The problem is when I raised this issue on Micro$oft's forums, they are saying that my assessment was correct - that the formula looks for the 'current' file's name when processing the "filename" keyword parameter of the CELL function so, if it is not working when used 'within some third party's sharing facility', then it must be that third-party's sharing or syncing process that is causing the problem... In other words... they are pointing the finger at Dropbox... And Dropbox is saying it's a Microsoft Excel issue... and us poor end user's are left without a solution... Not trying to shoot or shout at the messengers... (I really do appreciate your efforts!!!) I'm just frustrated that I can't find a solution.
- Rich
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?
- MarkDropbox is no different to a USB pen or email.
It simply transfers the files between users. And it has the added bonus in it that hashchecks the files to make sure they are exact replicas of the original, unlike say a USB move. 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.
@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.
Update to my question below:
I found my solution here:THank you
