0

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.

  • Ditch structured references and use cell references instead. – teylyn Apr 12 '16 at 0:32
  • @teylyn Examples? – Mouthpear Apr 12 '16 at 11:45
0

Don't use structured references, use cell references instead.

With State in column A and Origin in column B, first row has column headers, use =MAX(SEARCH(A2,B2)). Copy the formula to any other table with the same layout and the cell references will adjust.

  • I cannot use Cell References. While the names of the headers are the same the Column number may change. So where in "Table1" the "Origin" header is in Column 2 and maybe start at D2, while in "Table2" it may be Column 4 and start at H12. This Particular Snippet repeat in formulas more times than I care to have to go change them for every table. – Mouthpear Apr 12 '16 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.