Trouble Using Pivot Table off a DNR

I would like not to have to update the source data for the pivot table in my database. I've read that you can solve this by using a Dynamic Named Range and using that as the source for the pivot table. That way the pivot table will expand as new data is added to the database. The formula I used to create the DNR is:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:A$),78)

There are 78 columns in the database. And there are 100 rows in the database. However, for some reason, the formula only picks up the first 73 rows in the database. So the formula has to be wrong. But where? How do I get it to pick up all the rows in the database?

Thanks.

Your Dynamic Named Range should be defined as:

Names in workbook: DNR1
Refers to: =OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),72)

Does that help?

It does represent that, but there are only 72 columns in your Data Sheet.

It should work with 78 too... if you intend to add those columns.

When I opened your workbook and looked at your original named range reference... I saw #REF! errors... so you must have done something wrong when creating the dynamic range formula.

=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),78) should work too.
anyways...

Originally Posted by NBVC

It should work with 78 too... if you intend to add those columns.

When I opened your workbook and looked at your original named range reference... I saw #REF! errors... so you must have done something wrong when creating the dynamic range formula.

=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),78) should work too.
anyways...

Thanks very much. That helps a lot.

You're welcome.

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save