How to select a Dynamic Named Range without VBA

[MrDB4Excel]

The range you see with headings in row "1", now encompassing rows 1 through 10, changes regularly.
I want to be able to select this dynamic range but only select from row 2 through to the last non-blank row (in this case row 10).
And how do I want to select this dynamic range? By picking a single cell elsewhere on the sheet, for example, either with a link cell or a button that gets (selects) all content within the dynamic range below the headings in row "1" so it can be cleared to make empty cells for new data that is copied from another workbook. The link cell labeled "Get ClearContentDateSort" now only selects the static range A2:E10, but if the new data coming in goes down to for example row "!2" then picking this link cell will still only select A2:E10.
As you can see by the formulas above I have tried several things but they all do not do what I want.
Any help will be much appreciated.

DateSort.xlsx
	A	B	C	D	E	F
2	09/16/2021 10:46:06	John Doe 1	Subject 1	John Doe 2	863 >
3	09/16/2021 16:23:48	John Doe 2	Subject 2	John Doe 1	864 >
4	09/16/2021 20:36:13	John Doe 1	Subject 1	John Doe 2	865 >
5	09/17/2021 10:03:48	John Doe 2	Subject 2	John Doe 1	866 >
6	09/17/2021 11:29:18	John Doe 2	Subject 2	John Doe 1	867 >
7	09/17/2021 18:30:01	John Doe 1	Subject 1	John Doe 2	868 >
8	09/17/2021 19:22:07	John Doe 2	Subject 2	John Doe 1	869 >
9	09/17/2021 19:29:01	John Doe 2	Subject 2	John Doe 1	870 >
10	09/18/2021 00:47:26	John Doe 1	Subject 1	John Doe 2	871 >
11
12
13						=OFFSET(DateSort!$A$1,0,0,COUNTA(DateSort!$A:$A),$1:$1)
14						=OFFSET($A$1,0,0,COUNTA($A:$A),1)
15						=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
16						=OFFSET(OutlookSortByDate!$A$2,0,0,SUMPRODUCT(MAX((OutlookSortByDate!$A:$A<>"")*ROW(OutlookSortByDate!$A:$A))),1)
17						=$A$2:INDEX($A:$A, COUNTA($A:$A))
18						=OFFSET(DateSort!$A$1,0,0,SUMPRODUCT(MAX((DateSort!$A:$A<>"")*ROW(DateSort!$A:$A))),1)
19
20						How to select a Dynamic Named Range without VBA
21						The range you see with headings in row "1", now encompassing rows 1 through 10, changes regularly. I want to be able to select this dynamic range but only select from row 2 through to the last non-blank row (in this case row 10). And how do I want to select this dynamic range? By picking a single cell elsewhere on the sheet, for example either with a link cell or a button that gets (selects) all content within the dynamic range below the headings in row "1" so it can be cleared to make empty cells for new data that is copied from another workbook. The link cell labeled "Get ClearContentDateSort" now only selects the static range A2:E10, but if the new data coming in goes down to for example row "!2" then picking this link cell will still only select A2:E10. As you can see by the formulas above I have tried several things but they all do not do what I want. Any help will be much appreciated.
22
23						Get DateSortClearContent
DateSort

 

[maabadi]

Get to Formula Tab Select Name Manager, Select New
Then at the Name Box add name for Dynamic Range
At The Refer to Box add This ( Change Sheet1 to your sheet Name)

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A) - 1,COUNTA(Sheet1!$1:$1))

Press OK and Then Close.
Now, When you input Dynamic Range Name at the Name Box of Excel ( where the Cell address appears = Left of Formula bar) and Press Enter
Your Dynamic Range Automatically Select.

 

[MrDB4Excel]

Thanks. Works great. Now is there a way to populate that name box that is to the left of the formula bar via either a formula or a cell pick that causes the range to be selected or some kind of simple method other than picking in the name box and entering the Dynamic Range Name? And of course without using VBA.

 

[maabadi]

For formula yes, Only Replace of Dynamic Range Address with Name you defined.
for selecting dynamic range, I don't know any way, maybe others have options.

AND why you don't use Table for Range, Select one Cell in Range and then Press CTRL+T
You can change Table Name and Properties at Design Tab.
Also it is dynamic if add rows, it changes table size.
Then When you want to Input formula at any cell and select header row or column when see black arrow, its name automatically added to formula

 

[maabadi]

I edit my previous post, check again.
You're Welcome & Thanks for Feedback.

 

[MrDB4Excel]

It got me to thinking about another formula I am using in another workbook. So I copied that formula and inserted it in this workbook I am testing as follows:
=HYPERLINK("#CCDS","Get ClearContentDateSort")
CCDS is the Dynamic Range Name and when I added rows or deleted rows it works perfectly.
Many thanks maabadi.
I guess I only have one thing that may mess it up and that is if the area I want to do this to in another workbook is a table.
I just now made it work with a table in the other workbook.
Kudos to maabadi

 

[maabadi]

You're Welcome again.