Excel Return Table name using Formula?

Question

I was wondering if there is anyway to return the name of the table using a formula?

I was working on a way to break down a few thousand addresses into there perspective column of information. ie.. #, Street, City, State, Zip-code and Phone#. The addresses are not in any consistent format that Text to Columns would work. I finally came up with the formulas to get the job done, but the are very long. In a post I found it suggested to use repeated parts of the formulas as a Defined Name. And it made it so much easier. Now Here is the problem.

A formula that has the table name "Table1" won't work in "Table2". Or any other table name. Column headers are the same for each table.

MAX(SEARCH(Table1[@State],Table1[@Origin]))

A way to return the name of the table is needed. Via formula or formula as Defined Name.

MAX(SEARCH(GetTableName[@State],GetTableName[@Origin]))

I prefer it to be a formula. I'm not sure if a VBA solution would be a correct answer to this question so I would not be able to choose it as THE answer, even if it does work. It will still be appreciated. I will ask in a separate post if I do not find a Formula Solution.

TY

I found this post that has a VBA solution, but I can't use it. I will post just so someone can maybe figure this out. Portland Runner Posted this CODE to get table name.

Function GetTableName(shtName As String) As String
    GetTableName = Worksheets(shtName).ListObjects(1).Name
End Function

In that Function I enter My Defined Name formula named "SheetName"

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)

So I can use it like this.

=MAX(SEARCH(INDIRECT(GetTableName(SheetName)&"[@State]"),INDIRECT(GetTableName(SheetName)&"[@Origin]")))

However I still need this to be Formula Only. While I can run Macros on My PC, they will not run in the PC that has all the data.

This is the last thing I got using a UDF. Unfortunately I still cant use it. Plus It gets the first Table's name and not the actual table the cell is in. Good if that is the only table in sheet or if the first table is the table you want.

Function GetTableName() As String 
    GetTableName = Worksheets(ActiveSheet.Name).ListObjects(1).Name
End Function

Solution 1

You will need two cells to get the table name. My Table Headers start in row 2 and Table Data in row 3, so I put my two formulas in cells A1 and B1, respectively.

The first cell should reference the top left header cell of your table. For me, the formula ends up reading:

=My2016Data[[#Headers],[State]]

and equates to "State".

The second cell's formula should be:

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

and equates to "My2016Data".

Solution 2

Here's a VBA solution since you said you want to see it. This is a UDF (user defined function) which you create with VBA but use as a formula inside a cell. Save this code in a standard code module (not in a sheet module and not in the "ThisWorkbook" module):

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

Once saved to a module, you can use it in a cell formula like this:

=GetTableName(A1)

or this

=GetTableName(B:B)

or this

=GetTableName(B2:W900)

If you use a range that overlaps more than one table it will return the name of the first table.

Comments

[Mouthpear]

Thank you. I have tried it and it works. I still can't use it for the original project I was working on.The computer with the information is a company computer and the use of macros or VBA was disabled. But I did find use for your UDF. I would really like to Check yours as the answer but I'm not sure if it is allowed since the question was about a formula solution. I wonder if the Moderators would allow it. Thank you very much either way.

[Ben Seymour]

I was surprised to find that this strategy actually works in practice, although the answer could be a little more clear. Basically, your first formula needs to be =<TableName>[[#Headers],[<SomeColumnInTable>]], and the second needs to be =MID(FORMULATEXT(<CellContainingFirstFormula>),2,FIND("[",FO‌​RMULATEXT(<CellConta‌​iningFirstFormula>))‌​-2). Because Excel automatically updates table names in formulas when the names change, this second formula will always show the table's current name, provided you don't change the first cell yourself.

[Ben Seymour]

Note: after changing a table name, you may need to force Excel to perform a recalculation. You can do this via Formulas->Calculate Now, or by opening the cell of the second formula for editing and pressing Enter.

[4b0]

Welcome to Stack Overflow! Welcome to Stack Overflow! Please try to provide a nice description about how your solution works. See: How do I write a good answer?. Thanks.

[Mouthpear]

This will not work. The table name is variable and i will not know the name. I was trying to write a formula that would be entered a computer that has macros disabled. The PC is not mine nor does it belong to my friend who uses it. It belongs to the company he works for. I (he) needs the formula to work in whatever table it happens to be in.

[Mouthpear]

I will check it out ASAP

[Micah Lindstrom]

Love this, albeit 2 small caveats - it uses volatile function INDIRECT, and it does not immediately update when table name changes. I made this expanded version to (1) add "Table: " text prefix and (2) convert underscores to spaces: ="Table:" & MID(SUBSTITUTE(LET(x,Manifold_Descriptions[#All], y, FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))), z, LEFT(y, FIND("[", y)-1), TRIM(RIGHT(z, LEN(z)-FIND("x", z)-1))), "_", " "), 6, 999). This returns Table: Manifold Descriptions, and (eventually) automatically updates with table name changes.