PDA

View Full Version : can a forumla return the name of a table



JYool
2011-08-29, 06:55 PM
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
2011-08-29, 10:57 PM
You could create a simple UDF like so


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

and test in the worksheet like this

=GetTable("Table1")

JYool
2011-08-30, 12:08 AM
will this work if there is more than one table in the active sheet?

Bob Phillips
2011-08-30, 09:57 AM
No, you need to loop all objecst for that



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