Excel Formula: Convert the name of a structured table to text

Question

I have a structured table called MyTable and the column called MyColumn.

MyColumn
aaa
bbb
ccc

I can write Excel formulas acting on some parts of this table that I select, like this: =Counta([MyTable][#All]) =Counta(FeaturesTbl[#Headers])

What I want is an Excel formula that will return the structured-table text of the cells I am selecting. Like this:

= MyFormula(SomeCellsISelected) this should return: "[MyTable][#All]" if I selected the entire table including headers.

The reason why I want this formula is that this table comes from a power query step, and its name may change from time to time without the user knowing. So I want to show to users what the current table name is, as well as use it dynamically in my VBA steps.

However, I want a native Excel formula for this.

I tried looking at CELL() but so far, it does not return the structured-table reference of the target cell.

Any ideas?

Answer 1

There are few methods to get the Table name.

1. All tables available, are listed in the Name Box.
2. Table's names can be found in the Formula Bar also.

But the most efficient method to get all Table's name form the current Workbook is VBA macro.

How it works:

3. Hit Alt+F11 to open the VB editor window.

4. From VB editor's window menu, click Insert then Module.

5. Copy & Paste below shown VBA code.

   Sub ListAllTables()
   
   Dim xTable As ListObject
   Dim xSheet As Worksheet
   Dim I As Long
   I = -1
   Sheets.Add.Name = "Table Name"
   For Each xSheet In Worksheets
       For Each xTable In xSheet.ListObjects
           I = I + 1
           Sheets("Table Name").Range("A1").Offset(I).Value = xTable.Name
       Next xTable
   Next
   End Sub
   

N.B

In this code new sheet name and cell reference for Table List (A1) are editable.

1. Press Alt+Q to return to file.

2. Save the Workbook as Macro Enabled.

3. Finally RUN the Macro.

You get a new sheet named as Table Name and list of Tables.

---

:Edited:

If you want to get one Table name at time, using the cell references, then you may use this UDF (User Define Function).

Function GetTableName(cellInTable As Range) As String
    Dim tblName As String
    tblName = vbNullString
    On Error Resume Next
    tblName = cellInTable.ListObject.Name
    GetTableName = tblName
End Function

How it works:

• Copy & Paste this VBA code as Module.
• Use this formula:

=GetTableName(H28)

• Where H28 is cell reference of top left most cell from where the Table starts.

---

Using formula to get Table's name is bit complicated as well it deal with one Table as a time, but you can use this one.

How it works:

• In any blank cell, for example in J37, enter = sign and click the top left most cell of the Table, for example = and then click H28.

• You get this =Table6[[#Headers],[data]].

• Finally use this formula:

   =MID(FORMULATEXT(J37),2,FIND("[",FORMULATEXT(J37))-2)
  

• You get the Table name.

Adjust cell references in the formula as needed.