Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Ranges, Dynamic Fills - No Macros (Excel 200, SP3)

    Good Morning, Lounge!

    I have a list of data summarized wonderfully by a Pivot Table. From that, I've created a Pivot Chart. Unfortunately, the Pivot Chart doesn't maintain formatting over PT refreshes. Further, I need to copy that chart to a presentation; that carries all the extraneous buttons, fields, etc. and looks terrible. To get around this, I am trying to make a regular chart from the data. If the chart references the PT, Excel assumes I want a Pivot Chart, however. I tried, then, to create a dynamic range from the PT. Using this range for the chart still gives me a Pivot Chart.

    Finally, I decided to try to use the dynamic range to fill cells in another worksheet, then make a chart from that range. The problem is, how do I transfer the dynamic range data into the cells on the other worksheet? In other words, how do I get the information in the other worksheet to update as the pivot table (and thus the dynamic range) grows and shrinks? I can do this with a macro, but that presents user problems with warnings, security, etc.

    Thanks in advance for all tips, tricks, etc.

  2. #2
    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

    Re: Dynamic Ranges, Dynamic Fills - No Macros (Excel 200, SP3)

    Your best bet is going to be to use code to update the pivot chart's appearance whenever it is refreshed I think. You can create a normal chart from a pivot table but it actually references the ranges the pivot table happens to be occupying and doesn't adjust if the pivot table range changes. If the columns are always the same, you could use dynamic range names to pick up all the new data.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Ranges, Dynamic Fills - No Macros (Excel 200, SP3)

    Thanks, Rory. But are there any Excel functions that will fill in a block of cells the size of an array? For example, if my pivot table is 30 rows by 4 columns and I give it a named range, is there a function that will let me refernece the named range in another sheet in say, A1, and fill that sheet with the 30 rows and 4 columns of data? I know I can use INDIRECT, MATCH, or simply reference a cell, but all these functions would require me to fill the 30 row, 4 column area with the function; I'm hoping to simply put a function in a single cell and have it fill the 30x4 area.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Ranges, Dynamic Fills - No Macros (Excel 200, SP3)

    No, formulas don't work that way. You'd use code for that, but you didn't want to use code... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  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

    Re: Dynamic Ranges, Dynamic Fills - No Macros (Excel 200, SP3)

    You could use OFFSET or INDEX with references to the column/row your are in and a subtraction for the difference in the col/row from the source and destination.

    If you created an array formula and select the 30x4 region and confirmed with ctrl-shift-enter, you would fill the range based on only 1 cell...[Though you have to select the range before entering the formula, so I imagine this not what you want....]

    Steve

Posting Permissions

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