Dynamic Named Range

[nirvehex]

Hello all,

I'm trying to create a dynamic named range to use as a source for my pivot table data. I've attached my xlsm doc to make it easier to follow.

Essentially, I have a sheet titled, 'Long'. Towards the bottom under week ending 7/22 from cells B128:T129 I need to make it a named range to act as a source for my pivot table. Each week I post a new data set so for example next week ending, 7/29 the named range would be from cells B161:T162.

So my question is, in the Refers to box of a named range, what code do I write so it keeps changing?

Thanks,

Mark

[Kenneth Hobson]

=INDIRECT("Long!$B$"&(COUNTA(Long!$B:$B)-1)&":$B$"&COUNTA(Long!$B:$B))

[nirvehex]

Kenneth,

This sort of worked. Only this range started at Week Ending 7/29 and only highlighted B161:B162. I need it to start at B128:T129, and do B161:T162, and so forth as I add more rows. Is this possible?

Here's what I used for selecting a previous range: =Long!$A$1:INDEX(Long!$A:$IV,MATCH(9.99E+307,Long!$A:$A),MATCH(REPT("Z",255),Long!$1:$1)). This goes through my entire worksheet and is used a source for my pivot table to return only a certain range of cells which is modified each week. I just need to select part of my table now so I need to modify this to select just the ranges I mentioned above.

Thanks,

Mark

[Kenneth Hobson]

If you want the B and T then:
=INDIRECT("Long!$B$"&(COUNTA(Long!$B:$B)-1)&":$T$"&COUNTA(Long!$B:$B))

I don't know what the rules are for selecting previous range.

[royUK]

You are using a Table in the sheet. The Table name can be used as the source for the PivotTable, the table will expand as Rows are added

[nirvehex]

You are using a Table in the sheet. The Table name can be used as the source for the PivotTable, the table will expand as Rows are added

Roy,

The table does not encompass those cells. I also need to make the dynamic range include previous cells. Essentially, I have a sheet titled, 'Long'. Towards the bottom under week ending 7/22 from cells B128:T129 I need to make it a named range to act as a source for my pivot table. Each week I post a new data set so for example next week ending, 7/29 the named range would be from cells B161:T162.

So my question is, in the Refers to box of a named range, what code do I write so it keeps changing? As of now the code that Kenneth gave me, =INDIRECT("Long!$B$"&(COUNTA(Long!$B:$B)-1)&":$T$"&COUNTA(Long!$B:$B)) works for one of the data sets, B161:T162, but does not include B128:T129 and will not include more as I add more.

I also combined some earlier code that I had with Kenneth's code to come up with, =Long!$B$1:INDIRECT("Long!$B$"&(COUNTA(Long!$B:$B)-1)&":$T$"&COUNTA(Long!$B:$B)). This selects my worksheet instead of the two summary rows that I need to select. So each week I will have summary rows that take up the last two rows of my data set. I need to source just these two rows each week for a pivot table. I'm hoping to create a dynamic range that just selects those two rows from each week. Then I will filter my pivot table by week ending to see these results.

Thanks for your help!

-Mark


Thanks,

Mark

[Kenneth Hobson]

To get the previous:
=INDIRECT("Long!$B$"&(COUNTA(Long!$B:$B)-35)&":$T$"&COUNTA(Long!$B:$B)-34)