A formula to return Table Name by providing the Sheet name and Cell address

Question

Can anyone help me?

Excel file has 20 sheets

Each sheet has multiple tables

On a new sheet,

Type Sheet Name in Cell A1 - e.g. NorthSales

Type Cell address in Cell A2 e.g. H484

Cell A3 formula should return name of table in sheet Northsales that has cell H484 in it

THANK YOU!!

This is probably easier to do with VBA. – BigBen Mar 10 '20 at 21:11 — BigBen, Mar 10 '20 at 21:11

Vijay Suri · Answer 1 · 2020-03-11T15:09:44.137

I found my answer in another post: Excel Return Table name using Formula?

I used a variation of this solution for my problem:

I added a formula in each sheet that pointed to a header in the table
Then I referenced it in my new Sheet and extracted the table name using FORMULATEXT, INDIRECT and MID function

      A                          B                                    C                      D
1 SheetName       Formula pointing to each sheet             Extract table Name          Value in cell X2 in each sheet
2 
3 Sheet1       =FORMULATEXT(INDIRECT("'" & A3 &"'!X2"))   =MID(B3,2,FIND("[",B3)-2)  =Status_2022[[#Headers],[Days Open]]
4 Sheet2       =FORMULATEXT(INDIRECT("'" & A4 &"'!X2"))   =MID(B4,2,FIND("[",B4)-2)  =Status_2021[[#Headers],[Days Open]]
5 Sheet3       =FORMULATEXT(INDIRECT("'" & A5 &"'!X2"))   =MID(B5,2,FIND("[",B5)-2)  =Status_2028[[#Headers],[Days Open]]

</div>

score 0 · Answer 2 · answered Mar 10 '20 at 21:48

Consider the following User Defined Function:

Public Function GetListObject(sh As String, addy As String) As String
    Dim rng As Range, rngLO As Range, LO As ListObject
    Dim msg As String
    Dim sht As Worksheet

    Set sht = Sheets(sh)
    Set rng = sht.Range(addy)

    If sht.ListObjects.Count = 0 Then
        GetListObject = "No ListObjects in sheet " & sh
        Exit Function
    End If

    msg = "cell " & addy & " not in a ListObject"
    For Each LO In sht.ListObjects
        Set rngLO = LO.Range
        If Not Intersect(rng, rngLO) Is Nothing Then
            msg = LO.Name
            Exit For
        End If
    Next LO

    GetListObject = msg
End Function

If the sheet referenced has no tables, that is revealed.
If the cell referenced is not in a table, that is revealed. Otherwise the table name is returned.

A formula to return Table Name by providing the Sheet name and Cell address

2 Answers2