I was wondering if there is anyway to return the name of the table using a formula?
I was working on a way to break down a few thousand addresses into there perspective column of information. ie.. #, Street, City, State, Zip-code and Phone#. The addresses are not in any consistent format that Text to Columns would work. I finally came up with the formulas to get the job done, but the are very long. In a post I found it suggested to use repeated parts of the formulas as a Defined Name. And it made it so much easier. Now Here is the problem.
A formula that has the table name "Table1" won't work in "Table2". Or any other table name. Column headers are the same for each table.
MAX(SEARCH(Table1[@State],Table1[@Origin]))
A way to return the name of the table is needed. Via formula or formula as Defined Name.
MAX(SEARCH(GetTableName[@State],GetTableName[@Origin]))
I prefer it to be a formula. I'm not sure if a VBA solution would be a correct answer to this question so I would not be able to choose it as THE answer, even if it does work. It will still be appreciated. I will ask in a separate post if I do not find a Formula Solution.
TY
I found this post that has a VBA solution, but I can't use it. I will post just so someone can maybe figure this out. Portland Runner Posted this CODE to get table name.
Function GetTableName(shtName As String) As String
GetTableName = Worksheets(shtName).ListObjects(1).Name
End Function
In that Function I enter My Defined Name formula named "SheetName"
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)
So I can use it like this.
=MAX(SEARCH(INDIRECT(GetTableName(SheetName)&"[@State]"),INDIRECT(GetTableName(SheetName)&"[@Origin]")))
However I still need this to be Formula Only. While I can run Macros on My PC, they will not run in the PC that has all the data.