Results 1 to 4 of 4

Thread: can a forumla return the name of a table

  1. #1
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0

    can a forumla return the name of a table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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+

  2. #2
    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")

  3. #3
    Seeker JYool's Avatar
    Join Date
    Aug 2011
    Location
    Courtenay, BC, Canada
    Posts
    17
    Articles
    0
    will this work if there is more than one table in the active sheet?

  4. #4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •