+ Reply to Thread
Results 1 to 3 of 3

Refreshing a Pivot Table from a dynamic range

  1. #1
    Forum Contributor
    Join Date
    03-14-2005
    Posts
    164

    Refreshing a Pivot Table from a dynamic range

    Hi there,
    I have a pivot table on one worksheet, that is refreshed from a range of data on a second worksheet.

    the data is refreshed using a query, so can contain different numbers of rows.

    how can I code it to always refresh using the complete range from the data worksheet?

    can anyone help?

  2. #2
    Richard
    Guest

    RE: Refreshing a Pivot Table from a dynamic range

    If you use a named range (Insert --> Name --> Define) and under 'Refers to'
    use the offset formula to define a range (eg:
    =OFFSET(Data!$A$1,0,0,MAX(2,COUNTA(Data!$A:$A)),11) - the max statement
    ensures that if your data range returns no data, the pivot table will still
    work. Look under Help for details of how to use the offset function properly)

    Then use the named range as your pivot table source data.

    This assumes that you are using formulas at the side of the query to process
    the data further. If all of your data is in the query, then you can use the
    name of the query as the data range for the pivot table.

    To have everything automatically update when you refresh the query, you need
    to disable the background refresh in the query (under data range properties
    --> Refresh control) , and in table options on the pivot table, check the box
    by 'refresh on open'

    I hope that helps!

    Richard

    "matpj" wrote:

    >
    > Hi there,
    > I have a pivot table on one worksheet, that is refreshed from a range
    > of data on a second worksheet.
    >
    > the data is refreshed using a query, so can contain different numbers
    > of rows.
    >
    > how can I code it to always refresh using the complete range from the
    > data worksheet?
    >
    > can anyone help?
    >
    >
    > --
    > matpj
    > ------------------------------------------------------------------------
    > matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
    > View this thread: http://www.excelforum.com/showthread...hreadid=477871
    >
    >


  3. #3
    Tom Ogilvy
    Guest

    Re: Refreshing a Pivot Table from a dynamic range

    Is there a defined name for the table, something like External_Range1 or
    something close to that.

    Use that name as the source for you pivot table.

    --
    Regards,
    Tom Ogilvy


    "matpj" <matpj.1x7euf_1129817121.0103@excelforum-nospam.com> wrote in
    message news:matpj.1x7euf_1129817121.0103@excelforum-nospam.com...
    >
    > Hi there,
    > I have a pivot table on one worksheet, that is refreshed from a range
    > of data on a second worksheet.
    >
    > the data is refreshed using a query, so can contain different numbers
    > of rows.
    >
    > how can I code it to always refresh using the complete range from the
    > data worksheet?
    >
    > can anyone help?
    >
    >
    > --
    > matpj
    > ------------------------------------------------------------------------
    > matpj's Profile:

    http://www.excelforum.com/member.php...o&userid=21076
    > View this thread: http://www.excelforum.com/showthread...hreadid=477871
    >




+ 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