How to Show Excel Table Name on the Sheet

If you have an Excel workbook with lots of tables and pivot tables, it can be hard to remember what they’re named, or what source data they’re using. To help you keep track of them, AlexJ is sharing the User Defined Function (UDF) that he uses to help manage his files. With AlexJ’s code in your file, just add a formula, click on a cell, and show Excel table name, or pivot table name, on the sheet.

The code is shown below, and there is also a link for downloading a sample file with the code installed in it. You can see more of AlexJ’s sample files on my website.

Copy the Show Excel Table Name Code

To use this UDF in your file, copy the code shown below, and paste it into a regular code module in your workbook. There are instructions on my website, if you’re not sure how to do that.

Here is the code from AlexJ, to show an Excel table name on the sheet:

Function getObjName(rng As Range) As String
  Dim tbl As ListObject
  Dim pt As PivotTable
  Dim PivotName As String
  Dim TableName As String
  Dim qName As String
  Dim ptsName As String
  
  On Error GoTo Err_NoTable
  Set tbl = rng.Cells(1).ListObject
  TableName = "Table [" & tbl.Name & "]"
  
  On Error GoTo Err_NoQuery
  qName = "[" & _
    tbl.QueryTable.WorkbookConnection.Name _
      & "]"
        
Res_Table:
  TableName = TableName & qName
        
Res_Pivot:
  On Error GoTo Err_NoPivot
  Set pt = rng.Cells(1).PivotTable
  PivotName = "Pivot [" & _
    rng.Cells(1).PivotTable.Name & "]"
        
Res_PTS:
  On Error GoTo Err_NoPTSource
  ptsName = "[" & pt.SourceData & "]"
        
Res_PTName:
  PivotName = PivotName & ptsName
        
XIT:
  getObjName = TableName & PivotName
  Set tbl = Nothing
  Set pt = Nothing
  Exit Function
        
Err_NoTable:
  'Not a table check for Pivot
  TableName = ""
  Resume Res_Pivot
  'Resume
        
Err_NoQuery:
  'No Query on the table
  qName = ""
  Resume Res_Table
        
Err_NoPivot:
  'Not a Pivot Table - exit
  PivotName = ""
  Resume XIT
        
Err_NoPTSource:
  'No Pivot source identified
  ptsName = ""
  Resume Res_PTName

End Function

How to Use the Show Table Name Function

After you paste the Show Excel Table Name UDF code into your workbook, it’s ready to use. You can follow the steps below, to show the information about any table or pivot table in your file.

In this example, there are a couple of empty rows above the pivot table, so I added the formula there, in cell B1.

  1. Select the cell where you want to see the table name or pivot table name.
  2. Type an equal sign and the UDF name, followed by an opening bracket:  =getObjName(
  3. Then, click on a cell in the table or pivot table, to refer to that cell
    • udftablename02
  4. To complete the formula, press Enter

Excel will automatically add the closing bracket, and the formula displays the table or pivot table information.

The Formula Results

In the screen shot below, the formula referred to a pivot table cell, and you can see the formula results in cell B1.

  • NOTE: If the table name changes, the formula won’t update immediately. It will update when the workbook calculates.

There are three sections in the results, underlined and numbered in the screen shot.

  1. The type of object – Table or Pivot
  2. The object’s name
  3. The object’s source name, if available

show Excel table name on sheet

In the next screen shot, the formula refers to a cell in a named table. The data is typed into the table, so there isn’t a source name available.

udftablename04

If the formula refers to a cell that isn’t in a named Excel table or a pivot table, the formula result will be an empty string.

udftablename03

Download the Sample File

To download the sample file, and test the Show Excel Table Name code, go to the AlexJ Sample Files page on my Contextures website. In the VBA section, look for VBA0003 – Show Table or Pivot Name on Sheet.

The zipped file is in xlsm format, and contains a macro – the Show Excel Table Name UDF code.

___________

You may also like...

4 Responses

  1. Dazy Parker says:

    Helpful information.

  2. Hugo says:

    Great solution.
    Great explanation.
    Thanks.

  3. Bill McNair says:

    Thank you.

Leave a Reply to Dazy Parker Cancel reply

Your email address will not be published. Required fields are marked *