#VALUE error regarding filename

Question

Hello,

I tried Googling for help with for this #VALUE error but had no luck. I don't see the #VALUE error when I open the file on my computer on a network drive, but do see it when I open on Excel online or on my cell phone (android) that was emailed. I opened on another laptop not on our network via email attachment, and do not see the error. Another person who opened it on our network from their desktop is also seeing the error (so it is not due to it being viewed on a mobile device). The #VALUE error is as follows:

"Function CELL parameter 1 value is filename. it should be one of: 'COL', 'PREFIX', 'COLOR', 'ADDRESS', 'WIDTH', 'ROW', 'TYPE', 'CONTENTS'.

I have a few formulas written out on one tab of a workbook so that they display numbers that update monthly,  text that stays the same, and the month automatically updates based on the file name (e.g. December 31, 2018), all in one sentence (it is for these reoccurring footnotes for a financial report) for example:

="      implied leverage/financing of "&TEXT(E15,"$#,##0_);($#,##0)")&","  &TEXT(E23, "$#,##0_);($#,##0)") &","  &TEXT(E30, "$#,##0_);($#,##0)") &","  &TEXT(E42, "$#,##0_);($#,##0)")&"and "  &TEXT(E36, "$#,##0_);($#,##0)")&" million, respectively, at "&TEXT($J$53,"[$-en-US]mmmm d, yyyy;@")

displays on another tab in the workbook as: (where $xx is the amounts that change each month, referring to the cells above)

implied leverage/financing of $xx, $xx ,$xx ,$xx and ($xx) million, respectively, at December 31, 2018

And the last piece of text is referring to the filename in a separate cell, J53: TEXT($J$53,"[$-en-US]mmmm d, yyyy;@")

Where J53 (the month end) is:

=MID(CELL("filename",A50),SEARCH("[",CELL("filename",A50))+1,SEARCH(".",CELL("filename",A50))-1-SEARCH("[",CELL("filename",A50))) 

**I am thinking it has to do with this formula that populates the filename for me; I honestly don't understand it, (found it online) so this may be what is causing the issue?

Any ideas on how to fix this error so that the sentence always displays & not the #VALUE error?

p.s. I did not know of a better way to return the file name or these sentences, so any suggestions there are appreciated too! Sorry upfront if this is an obvious answer to someone, and I provided TMI!

Answer 1

According to the documentation for the CELL function (https://support.office.com/en-us/article/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf), the "filename" argument does not work Excel in Online, Excel Mobile, and Excel Starter.

Answer 2

Thanks HansV! So, if the user who was viewing on the desktop computer tried to print the file from Excel Starter, would the printed out file also display the #VALUE errors? They said it occurred both when viewing the actual file and when printing it out...so if they were to open it up fully (not using Excel Starter) and print, that should work?

Answer 3

It should work correctly if the workbook is opened in Excel for Windows (but not the Starter Edition) or Excel for Mac.