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.

Improve this question
2
  • Ditch structured references and use cell references instead.
    – teylyn
    Commented Apr 12, 2016 at 0:32
  • @teylyn Examples?
    – Mouthpear
    Commented Apr 12, 2016 at 11:45

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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
    Commented Apr 12, 2016 at 23:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .