Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Dynamic Range of Data for Pivot Tables

    My workbook has a sheet onto which I add new rows of data weekly. There is another sheet in the workbook with several pivot tables. What steps do I use to make the pivot tables refer to include the new rows of data with the existing data?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    The answer is suggested by the title of your post.
    A Dynamic Range of data perhaps should use a Dynamic Rangename as the source for the pivot tables.
    Thus, when you add new rows of data, the dynamic range reference automatically includes the added rows.

    Suppose the data extends from say column [A] to column [P], and starts in row 1 to row whatever.
    Suppose that column [B] always contains an entry for a data row (for example, col [B] might be say, ItemRef, which is never blank or empty.
    Then you could create a rangename say, sourceBlock, and in the name "refers to" box use the formula
    ="a1" & counta("b1:b16536")

    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Well, my data range is A10:N9852 and growing weekly (by rows only, though). But I can't seem to apply the new range "data". I get "Data source reference is not valid." when I apply ="a1" & COUNTA("b10:b16536") to the range name "data".

    I've tried OFFSET a time or two but the width/height variables confound me.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Got it now! After Searching through the Lounge I tried this OFFSET formula
    =OFFSET('AP Detail'!$A$10,0,0,COUNT('AP Detail'!$A:$A),14), but realized that it wasn't including the last row of freshly added data. So I modified the variable,COUNT('AP Detail'!$A:$A) to ,COUNT('AP Detail'!$A:$A)+1 and it worked!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You could probably use Counta (without the +1) since count does not include any text. I presume that the values are numbers and the label is text.

    Steve

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If you are using a version later than 2003, you could also convert the data to a table and use the table name as the data source. It grows dynamically anyway.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Based on your data range, the formula for the named range would be
    ="a10:N" & counta("b10:b16536")

    ..my original reply couldn't get the "colon" in

    zeddy

Posting Permissions

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