+ Reply to Thread
Results 1 to 2 of 2

Why do I always get a blank row / column with a pivot against a dynamic named range?

  1. #1
    Registered User
    Join Date
    12-16-2011
    Location
    Mill Valley, California
    MS-Off Ver
    Excel 2011
    Posts
    11

    Why do I always get a blank row / column with a pivot against a dynamic named range?

    I know that to SOME extent dynamic named ranges have been superceded for pivots using Lists / Tables (depending on your version), but Lists and Tables aren't perfect for dynamic pivot ranges. Notably they don't allow for expansion TO THE RIGHT in your data source (new fields). So, I'm kind still interested in using dynamic named ranges using the OFFSET function in my pivots.

    But I've always wondered why whenever I use a dynamic range I have to deselect these artifact "blank" rows and columns that show up. If I look at the common offset formula for a dynamic range that probably most of us use (anchored on A1 on a data tab and then scans down and right to determine the lower right bound of the data source), it seems like the offset should NOT be picking up stray rows or columns. How can I avoid this?

    Why is this important to me?

    Well, if you add new field values and you've deselected blanks your new field value (which would result in a new row or column) does not show up automatically on a refresh unless you go in and select it. This is because you've deselected showing "all" since you don't want the blanks. Kind of an annoyance in something that otherwise works really well.

    Let me know what my workaround is... maybe there's some checkbox that I've been missing all these years.

  2. #2
    Registered User
    Join Date
    12-16-2011
    Location
    Mill Valley, California
    MS-Off Ver
    Excel 2011
    Posts
    11

    Re: Why do I always get a blank row / column with a pivot against a dynamic named ran

    OK, so I need to correct myself and maybe take this conversation (so far with myself!) in a new direction. When you have a table in Excel 2007, if add a field / column of data to the right of your data source (table) the pivot will in fact become aware that you have a new field available. It won't of course get added automatically to the pivot (how would it know if it's a value, row, or column?) but it is available to the pivot.

    So, that having been said, now I wonder why would you ever choose a dynamic range over a table as a conventional pivot data source. Dynamic ranges are awesom, but not sure they're needed for pivots in the newer Excels.

+ 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