Cell formula that returns the name of a table

[BrandonAS]

Couldn't find a built in formula to do this, and didn't want to write VBA. So found a way to get the name of a table in Excel. It's a little clunky and I'm hoping for a cleaner solution,but here it is. Used three functions: MID, FORMULATEXT, and INDEX.

Have a table with name tblHarry

1. cell A1: =index(tblHarry,1,1)
2. cell A2: =MID(FORMULATEXT(A1),8,FIND(",",FORMULATEXT(A1),1)-8)

value in cell A1: 52 (this is arbitrary, it's just the value in row 1, column 1 of the table)
value in cell A2: tblHarry

FORMULATEXT(A1) returns the string value "=index(tblHarry,1,1)"
Then the MID function just pulls out the table name from the text string.

The clunky part is FORMULATEXT requires a reference to a cell, one cannot put the formula into the argument of FORMULATEXT, so it requires two formulas to get the result: The cell with the index formula and the cell with the MID(FORMULATEXT(...) formula.

 

[63falcondude]

Do you plan on changing the table name?

 

[BrandonAS]

Do you plan on changing the table name?

Yes, as items are added to the file, each gets it's own table. Then a bunch of calculations are done on the data in the table. Having the name of the new table in a cell allows using INDIRECT to reference table values in the formulas calculations.