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.
Bookmarks