SOLVED

Formula not working after file move

%3CLINGO-SUB%20id%3D%22lingo-sub-3183527%22%20slang%3D%22en-US%22%3EFormula%20not%20working%20after%20file%20move%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3183527%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everybody.%26nbsp%3B%20I%20have%20a%20fairly%20simple%20365%20Workbook%20that%20is%20being%20used%20in%20the%20D%5CTop%20App%2C%20I%20am%20working%20on%20the%20file%20locally%20and%20all%20is%20fine.%26nbsp%3B%20However%20when%20I%20copy%20it%20to%20another%20OneDrive%20location%20I%20get%20problems%20with%20the%20formula%20I%20am%20using%20to%20insert%20the%20worksheet%20name%20into%20a%20specific%20cell.%26nbsp%3B%20The%20problem%20formula%20is%20%3A-%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(ISERROR(RIGHT(%40CELL(%22filename%22%2C%3C%2FSPAN%3E%3CSPAN%3E%24A%241%3C%2FSPAN%3E%3CSPAN%3E)%2CLEN(%40CELL(%22filename%22%2C%3C%2FSPAN%3E%3CSPAN%3E%24A%241%3C%2FSPAN%3E%3CSPAN%3E))-FIND(%22%5D%22%2C%40CELL(%22filename%22%2C%3C%2FSPAN%3E%3CSPAN%3E%24A%241%3C%2FSPAN%3E%3CSPAN%3E))))%2C%22This%20workbook%20never%20saved%22%2CRIGHT(%40CELL(%22filename%22%2C%3C%2FSPAN%3E%3CSPAN%3E%24A%241%3C%2FSPAN%3E%3CSPAN%3E)%2CLEN(%40CELL(%22filename%22%2C%3C%2FSPAN%3E%3CSPAN%3E%24A%241%3C%2FSPAN%3E%3CSPAN%3E))-FIND(%22%5D%22%2C%40CELL(%22filename%22%2C%3C%2FSPAN%3E%3CSPAN%3E%24A%241%3C%2FSPAN%3E%3CSPAN%3E))))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ETIA%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EJon%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3183527%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3183601%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20not%20working%20after%20file%20move%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3183601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1305380%22%20target%3D%22_blank%22%3E%40JonnieMorgan%3C%2FA%3E%26nbsp%3BTried%20to%20replicate%20what%20you%20described%20and%20encounter%20no%20problems.%20By%20the%20way%2C%20uou%20can%20shorten%20the%20formula%20by%20using%20IFERROR%20rather%20then%20combining%20IF%20and%20ISERROR.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFERROR(RIGHT(%40CELL(%22filename%22%2C%24A%241)%2CLEN(%40CELL(%22filename%22%2C%24A%241))-FIND(%22%5D%22%2C%40CELL(%22filename%22%2C%24A%241)))%2C%22This%20workbook%20never%20saved%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Everybody.  I have a fairly simple 365 Workbook that is being used in the D\Top App, I am working on the file locally and all is fine.  However when I copy it to another OneDrive location I get problems with the formula I am using to insert the worksheet name into a specific cell.  The problem formula is :-

=IF(ISERROR(RIGHT(@CELL("filename",$A$1),LEN(@CELL("filename",$A$1))-FIND("]",@CELL("filename",$A$1)))),"This workbook never saved",RIGHT(@CELL("filename",$A$1),LEN(@CELL("filename",$A$1))-FIND("]",@CELL("filename",$A$1))))

 

TIA

 

Jon

 

4 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@JonnieMorgan Tried to replicate what you described and encounter no problems. By the way, you can shorten the formula by using IFERROR rather then combining IF and ISERROR.

 

=IFERROR(RIGHT(@CELL("filename",$A$1),LEN(@CELL("filename",$A$1))-FIND("]",@CELL("filename",$A$1))),"This workbook never saved")

 

 

Hi thanks for your reply, and thanks for the tip, I'll change that. (Not my formula I pinched it!!) Thats Interesting, I should have said it happens in Excel Web App as well.
Weird one, I changed it to your edit and it's fine now. Many thanks.
Jon