Dynamic Named Range With 2 Columns

[wsnyder]

Hi all,

I am trying to create a Dynamic Named Range from 2 Columns, but it keeps returning all columns

First I tried:

=Data!$U$8:INDEX(Data!$8:$1048576,COUNTA(Data!$U:$U),COUNTA(Data!$11:$11))

But returned too many columns, so I edited in Name Manager to this

=Data!$U$8:INDEX(Data!$8:$1048576,COUNTA(Data!$U:$U),COUNTA(Data!$U11:$V11))

Still returning more than 2 columns
I thought maybe because I edited the formula, so I just tried adding a new Dynamic Named Range?
No, still returning more than 2 columns.

What am I doing wrong here? Should just be $U:$V.

Thanks,
-w

 

[DanteAmor]

If you want only 2 columns:

=Data!$U$8:INDEX(Data!V1:V1000000,COUNTA(Data!$U:$U),0)

 

[wsnyder]

Thanks DanteAmor,

I updated my Dynamic Named Range based on your formula.
When I go to a new sheet in the workbook and type = nm_rng a spill array is returned with 17 columns
It seems to be picking up Columns A-Q, These columns are a mix of Pivot Tables, Blank Columns, and Formula Columns.
The last column in the Spill Array is Column U. Column V is not returned at all.

Thanks,
-w

 

[DanteAmor]

Hi

The $ was missing to make the range absolute.
Delete the name and create it new with the following:

=Data!$U$8:INDICE(Data!$V$1:$V$1000000,CONTARA(Data!$U:$U),0)