Formula to get table address

[drom]

Hi and Thanks in advance!

I do not want to use VBA

I have a table in a sheet
The table name is Table1
This table may contain X rows and Y columns

For this eg: B5;Z450.​

​

How Can I use a Formula to get this table range address ??
=CELL("address";Table1) is giving me only the upper left cell address so $B$5
and I am expecting to get B5;Z450 or $B$5:$Z$450

​

​

 

[Micron]

Maybe one of these methods?

Get address of named range or table

To get the address of a named range or an Excel Table as text, you can use the ADDRESS function. In the example shown, the formula in G5 is: =ADDRESS(@ROW(data),@COLUMN(data),4)&":"&ADDRESS(@ROW(data)+ROWS(data)-1,@COLUMN(data)+COLUMNS(data)-1,4) where data is the named range B5:E16. Note: Excel...

exceljet.net

 

[etaf]

does this work
=ADDRESS(@ROW(Table1),@COLUMN(Table1),4)&":"&ADDRESS(@ROW(Table1)+ROWS(Table1)-1,@COLUMN(Table1)+COLUMNS(Table1)-1,4)

Book2
	A	B	C	D	E	F	G	H	I	J
1	Column1	Column2	Column3	Column4	Column5
2								A2:E21
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sheet1

Cell Formulas
Range	Formula
H2	H2	=ADDRESS(ROW(Table1),COLUMN(Table1),4)&":"&ADDRESS(ROW(Table1)+ROWS(Table1)-1,COLUMN(Table1)+COLUMNS(Table1)-1,4)

Get address of named range or table

To get the address of a named range or an Excel Table as text, you can use the ADDRESS function. In the example shown, the formula in G5 is: =ADDRESS(@ROW(data),@COLUMN(data),4)&":"&ADDRESS(@ROW(data)+ROWS(data)-1,@COLUMN(data)+COLUMNS(data)-1,4) where data is the named range B5:E16. Note: Excel...

exceljet.net