+ Reply to Thread
Results 1 to 3 of 3

Pivot table dynamic range too large, unable to filter dates.

  1. #1
    Registered User
    Join Date
    08-02-2009
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2007
    Posts
    84

    Pivot table dynamic range too large, unable to filter dates.

    Hi all.

    I have my data in Sheet 1 columns A:T and currently down to row 1828 (Always expanding). All columns have header names in row 1.

    I am creating a pivot table with a named dynamic range using =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$T),20) which works, however when i select the data source, rather than selecting only the current data ending row 1828 it shows marching ants ending at row 30788 ?

    When working with my pivot i am unable to filter between dates or even group dates; i think because its trying to read a load of empty cells.

    I've made sure all those cells are free of data, and there are no gaps in the column filled with dates.

    I've searched for a possible solution but hitting a brick wall; any help would be much appreciated.

    Many thanks.

    Si

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Pivot table dynamic range too large, unable to filter dates.

    COUNTA(Sheet1!$A:$T) is counting all relevant cells in the entire selected range A:T!
    Return rows in column A:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-02-2009
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: Pivot table dynamic range too large, unable to filter dates.

    Quote Originally Posted by protonLeah View Post
    COUNTA(Sheet1!$A:$T) is counting all relevant cells in the entire selected range A:T!
    Return rows in column A:
    Please Login or Register  to view this content.
    Thanks protonleah

    That works.

    Any Idea why I am unable to filter between or group dates in my pivot?

    Regards

    Si

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Unable To Get The Pivotfields Property Of The Pivot Table when modify field filter
    By pjandliz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-13-2014, 11:40 PM
  2. Select Multiple Dates In Report Filter Pivot Table But Dates Are Not Static
    By biasedobserver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2014, 03:38 PM
  3. Unable to filter Dates in Pivot
    By nagesh.tvsr in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-11-2014, 09:41 AM
  4. Unable to group dates in a pivot table despite formatting the column
    By MaxwellR in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 12-11-2013, 07:12 PM
  5. Replies: 0
    Last Post: 05-12-2010, 05:01 PM

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