Dynamic Range of Rows

[Jeffrey Mahoney]

I'm trying to create a dynamic range by using the intersect of a range and a range of Rows. If I create this formula:
$A$6:$V$1000 6:20
I get a range of $A$6:$V$20 to work with

If I want to dynamically select the rows like this:
$A$6:$V$1000 6:Max(($A$6:$A$1000<>"")*row($A$6:$A$1000))
it won't let me create this formula

I know that Max(($A$6:$A$1000<>"")*row($A$6:$A$1000)) gives me the correct row number

I tried using the LET function to assign that to a variable, that's a no go also.

Is there a simple way to specify a range of rows dynamically?

 

[Tetra201]

Try

$A$6:$V$1000 INDIRECT("6:"&MAX(($A$6:$A$1000<>"")*ROW($A$6:$A$1000)))

 

[Eric W]

Or:

$A$6:INDEX($V:$V,MAX(($A$6:$A$1000<>"")*ROW($A$6:$A$1000)))

 

[Jeffrey Mahoney]

Thank you for that Tetra. Looks like Indirect isn't working in Named Ranges

 

[Tetra201]

What exactly isn't working? -- In my test workbook, I have created a name that refers to

=Sheet1!$A$6:$V$1000 INDIRECT("Sheet1!6:"&MAX((Sheet1!$A$6:$A$1000<>"")*ROW(Sheet1!$A$6:$A$1000)))

and it works OK inside, say, INDEX().

 

[Jeffrey Mahoney]

Tetra, I have other dependent named ranges that are actually the issue. Once the main table has been established I have select columns that I need to reference, so I use the Intersect method to create those, such as:
=MasterDataTable $A:$A

For some reason those get #VALUE inside of formulas until I change back to not using Indirect in the main table

 

[Jeffrey Mahoney]

Eric, I am using something very similar to your suggestion