can a forumla return the name of a table

JYool

New member
Joined
Aug 24, 2011
Messages
17
Reaction score
0
Points
0
Location
Courtenay, BC, Canada
Is there a formula that can return the name of a table on a worksheet? or anything in VB? I'm using the =cell(filename) formula to isolate the name of the worksheet, but i can't find something similar to isolate the name of a table... but it's a property of that table, so it has to exist somewhere...

either that or can i force a table to be named based on a cell using a macro?

it's important that the table be validated to a list (either by stopping wrong entries, or by hilighting errors) but it's imperative that users can actually see the table name...

I'm pretty strong in excel, but somewhat inexperienced in the features of 2007+
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You could create a simple UDF like so

Code:
Function GetTable(tablename)
    GetTable = ActiveSheet.ListObjects(1).Name = tablename
End Function

and test in the worksheet like this

=GetTable("Table1")
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
No, you need to loop all objecst for that

Code:
Function GetTable(tablename)
Dim idx As Long

    GetTable = False
    With ActiveSheet.ListObjects
    
        For idx = 1 To .Count
        
            If .Item(idx).Name = tablename Then
            
                GetTable = True
            End If
        Next idx
    End With
End Function
 
Top