How to solve #VALUE! Error when using Cell("filename")?

Question

Hello,

I cannot get the filename by cell function using cell("filename"). It says "A value used in the formula is of the wrong data type." I did not assign any data types to cells. I also tried with referencing (cell("filename";A1))

I can use cell function for other features like col.

How can I solve this?

Answer 1

Can you try using CELL("FILENAME";A1) in your formula? This issue has been reported for Turkish version of Excel where the above workaround was recommended. All arguments of CELL were working except filename. This might be a problem in few other non English versions of Excel.

Source - https://social.msdn.microsoft.com/Forums/office...

Answer 2

There are 2 possible reasons for the problem:

a) The file is stored in OneDrive or Sharepoint

b) Your Excel language is not English

I suggest to use an UDF

Function SheetName(ByVal Cell As Range) As String
  SheetName = Cell.Parent.Name
End Function

Call with a formula

=SheetName(A1)

Andreas.

Answer 3

Hello,

I just want a cell that shows sheet name for the sheet its on. 

I want this to be automated:

I was trying to use a formula I found online:

=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255)

Error occurs on cell function. 

Filename is irrelevant for me, if there is a more direct solution to get sheet name I would use it

Thank you,

Answer 4

Hi Volkan

May I ask, are you trying to get data from another Excel spreadsheet by referencing a file name in a cell?

If so, in that cell have you declared the correct path to that Workbook and also a sheet on that Workbook?

Please provide the text from that cell so I can check that is formatted correctly . . .