Dynamic named range

[Formula11]

How can I name a range which changes.
Range is 2-dimensional. Last row and column are as shown.

I used something like this in the past for a single column.

=Sheet1!$G$1:INDEX(Sheet1!$G:$G,COUNTA(Sheet1!$G:$G))

 

[jillrein]

You can use =Sheet1!$A$1:INDEX(Sheet1!$1:$1048576,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1)) however, it will not work on your sample data as it is locating the row number of the last used cell in column a and the last used in row 1. You would need to make sure that those cells are always filled. You can test it by going to a blank cell and typing =name and it will display the data in your table.

 

[kvsrinivasamurthy]

Try this ARRAY formula

=Sheet1!G2:INDIRECT("Sheet1!G"&MAX(($A$1:$G$15<>"")*(ROW($A$1:$G$15))))

OR
non volatile ARRAY formula

=Sheet1!G2:INDEX(Sheet1!G:G,MAX(($A$2:$G$15<>"")*(ROW($A$2:$G$15))))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[Formula11]

Thanks but I couldn't get any to work.
kvsrinivasamurthy, this is for a dynamic range so last column, may be before or after "G", last row may be before of after "15".
Initial cell stays the same.

 

[Alex Blakenburg]

You might want to update your profile since it determines what options are available to you.
eg the below works as in MS365 but will need to be an array formula in older versions.4

If you add the same logic for columns to @kvsrinivasamurthy's Index version and make the rows and columns bigger than you would ever need, that should work.

=Sheet1!A2:INDEX(Sheet1!$A$1:$Z$10000,
                                  MAX((Sheet1!$A$2:$Z$10000<>"")*ROW(Sheet1!$A$2:$Z$10000)),
                                  MAX((Sheet1!$A$2:$Z$10000<>"")*COLUMN(Sheet1!$A$2:$Z$10000)))

 

[Formula11]

Thanks Alex, but again it refers to an end row and end column.
I needed to use it with conditional formatting to color cells, so don't want to apply color to row 10000.

 

[Alex Blakenburg]

If you put it in a named range. The named range will return the reduced range using the last row & column with data in it.

 

[Formula11]

OK thanks for that.

 

[Alex Blakenburg]

The alternative will be to use VBA.

 

[Formula11]

OK thanks I'll give it a try with VBA. There are examples to find last row and column, and will need to find out how to create a name.

Also I tried to find an example of how to use a named range in conditional formatting rules but had no luck.