How to get the name of the table in a cell with a function?

**rsdoost** · 09-15-2015, 07:10 AM

Hi
I have made a database in excel with different sheets. each sheet has got one table in it. there is a "summary" sheet that I have summarized the data of all sheets in that.
I need to know the name of each table in each sheet automatically. I mean I want to use a function to extract the name of table in each sheet. I searched all of the functions but I did not find any related function. I will be appreciated if somebody can help me.
Best Regards
Rsdoost

**tim201110** · 09-15-2015, 08:16 AM

indirect________________

**rsdoost** · 09-15-2015, 08:23 AM

thanks for your reply
but I did not get it
Indirect function refers to cell address not name of table.
regards

**Kaper** · 09-15-2015, 10:27 AM

Originally Posted by rsdoost

Indirect function refers to cell address not name of table

Nope, You can use Table name too:

Formula:

=INDIRECT("Table1")

etc.

But this is the opposite direction - As I understand, you want to retrieve name of table. So my proposition is to use simple UDF:

Function firsttable(sheetname As String) As String
Dim dummystr As String
On Error Resume Next
  dummystr = Worksheets(sheetname).Name
  If Err.Number <> 0 Then
    firsttable = "! no worksheet: " & sheetname
    Exit Function
  End If
On Error GoTo 0
If Worksheets(sheetname).ListObjects.Count = 0 Then
  firsttable = "! no table in worksheet: " & sheetname
Else
  firsttable = Worksheets(sheetname).ListObjects(1).Name
End If
End Function

Insert this code in standard module (<Alt>+<F11> <Alt>+<I>,<M> ), make sure your spreadsheet has macros enabled and use as you would use any standard excel function in a worksheet. For instance:

Formula:

=firsttable("Sheet4")

**rsdoost** · 09-15-2015, 11:46 AM

hi
thank you for your reply
but I want to do that without vb or macro.
for example we use the following function for finding name of sheet.

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1))

I am looking for similar function for name of table.
regards

**rsdoost** · 09-16-2015, 12:48 AM

hi everybody
I did it myself
thx

**Kaper** · 09-16-2015, 02:26 AM

Great,
Please share your solution here for those who will look for it by http://www.excelforum.com/search.php etc.

**rsdoost** · 09-16-2015, 02:42 PM

correction
---------------------------
hi
suppose , there is a table in the sheet1 with headers column1,column2,... and cells A1, A2 and A3 are blank.
you can write a formula in A1 that is about table such as
=COUNTIF(Table1[column1],TRUE)

then you can write the following function in cell A2
=FORMULATEXT(A1)

at the end you can write this in cell A3
=MID(A2,FIND("(",A2,1)+1,FIND("[",A2,1)-FIND("(",A2,1)-1)

you will see the cell "A3" has got the name of table.

if you change the name of table manually in formula --> Name Manager then the content of cell "A3" will be changed.
regards

**rsdoost** · 09-16-2015, 02:41 PM

hi
suppose , there is a table in the sheet1 with headers column1,column2,... and cells A1, A2 and A3 are blank.
you can write a formula in A1 that is about table such as
=COUNTIF(Table1[TRUE],TRUE)

then you can write the following function in cell A2
=FORMULATEXT(A1)

at the end you can write this in cell A3
=MID(E5,FIND("(",A2,1)+1,FIND("[",A2,1)-FIND("(",A2,1)-1)

you will see the cell "A3" has got the name of table.

if you change the name of table manually in formula --> Name Manager then the content of cell "A3" will be changed.
regards

How to get the name of the table in a cell with a function?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Re: How to get the name of the table in a cell with a function?

Thread Information

Users Browsing this Thread

Similar Threads

If function to look up from a table on another sheet to return value from table

Need to refer a cell having range of another workbook in table array of vlook up function

[SOLVED] Help using "if" function to read from Data table and produce automatic input to cell value

Help creating vba function to move data from table to table on if condtion

linking table cell to another table cell without losing data when table is sorted

When changing a cell on table in Sheet1, index match vba function doesnt trigger in sheet2

How to use a cell value as Table Array in VLOOKUP worksheet function

Bookmarks

Bookmarks

Posting Permissions