How to get the name of the table in a cell with a function?

Originally Posted by rsdoost

Indirect function refers to cell address not name of table

Nope, You can use Table name too:

Formula:

=INDIRECT("Table1")

etc.

But this is the opposite direction - As I understand, you want to retrieve name of table. So my proposition is to use simple UDF:

Function firsttable(sheetname As String) As String
Dim dummystr As String
On Error Resume Next
  dummystr = Worksheets(sheetname).Name
  If Err.Number <> 0 Then
    firsttable = "! no worksheet: " & sheetname
    Exit Function
  End If
On Error GoTo 0
If Worksheets(sheetname).ListObjects.Count = 0 Then
  firsttable = "! no table in worksheet: " & sheetname
Else
  firsttable = Worksheets(sheetname).ListObjects(1).Name
End If
End Function

Insert this code in standard module (<Alt>+<F11> <Alt>+<I>,<M> ), make sure your spreadsheet has macros enabled and use as you would use any standard excel function in a worksheet. For instance:

Formula:

=firsttable("Sheet4")