CODEWITHSUNDEEP
excelformuladefinedtablename
Mouthpear
Mouthpear

Reputation: 329

Excel Return Table name using Formula?

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

Upvotes: 13

Views: 35276

Answers (6)

Joel Engelhardt
Joel Engelhardt

Reputation: 81

In a single cell, you can use this =TableName() formula to return a table name as text. For example, =TableName(TblAccounts) returns "TblAccounts" and updates as the table's name is updated!

With the introduction of the LAMBDA function in Excel, returning table name can be achieved with this function in the Name Manager:

Formula

=LAMBDA(reference, LET(thisFormula,FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))),
TEXTBEFORE(TEXTAFTER(thisFormula,"("),IF(ISERROR(FIND("[",ThisFormula)),")","["))))

Create a Name in the Name Manager (e.g., called "TableName") with the above formula in the "Refers to:" field. This custom function then takes a reference to the table and returns the table's name. This can use any table reference that includes the table name, as @Marat points out:

You can use any part of the table, not only Table1[#Headers]. For example, it could be: Table1[#All] or Table1[Column1] or Table1[[#Headers],[Column1]].

This solution offers broader compatibility for using this functionality in Excel Online web version, where VBA does not work.

If this formula does not automatically update after changing a table name, click "Calculate Now" under the Formulas tab on the ribbon to get the value to update. It is dynamic and will return whatever you name your tables, just as Excel changes the names in every other formula referencing a table!

How to use

After entering this formula in the Name Manager as TableName, type

=TableName(x)

where x is a reference that includes the name of the table. E.g., if my table is called "TblAccounts", x could be "TblAccounts", "TblAccounts[#Headers]", "TblAccounts[@Colulmn]", etc.

Explanation

Here is the code from above with indentation:

=LAMBDA(reference,
    LET(
        thisFormula, FORMULATEXT(INDIRECT(ADDRESS(ROW(), COLUMN()))),
        TEXTBEFORE(
            TEXTAFTER(thisFormula,"("),
            IF(ISERROR(FIND("[",ThisFormula)),
                ")",
                "["
            )
        )
    )
)

This LAMBDA function never actually references its parameter. Instead, the FORMULATEXT function returns the cell's formula, which looks like "=TableName(Table1[#Headers])."

The nested TEXTBEFORE(TEXTAFTER(... functions get the reference argument and trim down to just the table name. In case the table is referenced without square brackets, the IF function trims by the ")" character instead.

Upvotes: 6

Marat
Marat

Reputation: 51

Following formula returns table name, referenced in it:

=LET(x, Table1[#Headers], y, FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))), z, LEFT(y, FIND("[", y)-1), TRIM(RIGHT(z, LEN(z)-FIND("x", z)-1)))

Note: you can use any part of the table, not only Table1[#Headers].
For example it could be: Table1[#All] or Table1[Column1] or Table1[[#Headers],[Column1]].
The last one is useful, because you can just click a cell with a column name to paste it.

Upvotes: 1

PipM
PipM

Reputation: 11

If the tables are all on separate sheets, and the sheet names are the same as the table names, you could do something like this:

=LET(filename,CELL("filename",Table[#Headers]),RIGHT(filename,LEN(filename)-FIND("]",filename)))

This will return the sheet name that the table is on, which will match the table name.

Upvotes: 0

ChrisB
ChrisB

Reputation: 3205

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.

Upvotes: 6

Michael Saxe
Michael Saxe

Reputation: 1

=MID(
    FORMULATEXT(<CurrentCell>),
    53+2*<LengthOf<CurrentCell>>),
    LEN(FORMULATEXT(<CurrentCell>))-(53+2*<LengthOf<CurrentCell>>))-3
)&IF(0,<TableName>,"")

Upvotes: 0

Brad Emerick
Brad Emerick

Reputation: 91

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".

Upvotes: 9

Related Questions