+ Reply to Thread
Results 1 to 8 of 8

Trouble Using Pivot Table off a DNR

  1. #1
    Registered User
    Join Date
    11-07-2008
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2003
    Posts
    50

    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:
    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files
    Last edited by Research RN; 01-12-2009 at 05:34 PM. Reason: Solved

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Pivot Table based on Dynamic Named Range

    Your Dynamic Named Range should be defined as:

    Please Login or Register  to view this content.
    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    11-07-2008
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2003
    Posts
    50
    Quote Originally Posted by Ron Coderre View Post
    Your Dynamic Named Range should be defined as:

    Please Login or Register  to view this content.
    Does that help?
    Yes!!!

    Thanks. You're a lifesaver.

    But what does the "72" mean at the end of the formula? I thought it was supposed to represent the number of columns in the database.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    It does represent that, but there are only 72 columns in your Data Sheet.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    11-07-2008
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2003
    Posts
    50
    Quote Originally Posted by NBVC View Post
    It does represent that, but there are only 72 columns in your Data Sheet.
    Okay, I see that now. So basically Excel can not make a dynamic named range that includes blank columns at the end?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    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...

  7. #7
    Registered User
    Join Date
    11-07-2008
    Location
    St. Louis, Missouri, USA
    MS-Off Ver
    2003
    Posts
    50
    Quote Originally Posted by NBVC View Post
    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.
    Last edited by Research RN; 01-09-2009 at 12:03 PM. Reason: Solved

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1