=OFFSET(summary!$A2,,,COUNTA(summary!$A:$A),COUNTA (summary!$1:$1))

This works PROVIDING cells in Column A and row 1 have no embedded blanks.
Also assumes A is longest column.

"driller" wrote:

Good Day to all,

I have to make a dynamic named range(e.g. "test1") covering cell A1
down/right to the last cell that is not empty in my sheet1.
Criteria is....
1)named range : indirect("A1:"&"Maxcolumnreach (IS:IS) and MAXRowreach
(65535) and not empty"

so in my case, i need to embed the formula in the InsertNameDefineName
something like this....
Name: Test1
=indirect("A1:"&"Maxcolumnreach (IS:IS) and MAXRowreach (65535) and not empty"

so if the last non-empty cell is located on IR6000, the "Test1" range =
A1:IR6000...

i just dont know other function to work with this completely.

regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull