Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refresh chart (2002)

    I have a spreadsheet that contains 2 pages. Page1 has figures, Page 2 has a chart based on page 1 figures, and takes its charting information from a calculated rangeof numbers instead of a set of cells. I want to be able to do a filter, and then rechart based on the filtered information. When i try to refresh, or recalculate, the chart retains the information from the original full sheet. Is there a way to do this? If needed, i can post a copy of the spreadsheet. Thanks in advance for any help

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Refresh chart (2002)

    This ought to work...

    1 Click the chart.
    2 On the Tools menu, click Options, and then click the Chart tab.
    3 Select the Plot visible cells only check box.

    Regards,

    Jim Cone
    San Francisco, CA

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    Tried it, doesn't work. The chart information is taken from cells containing a formulae, not from a range of cells. I think that the cells with the formulae need to change in order to have the chart reflect the changed information. (Check attachment)
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    Hi,

    I see potential problems with your formulas on sheet Rentals.

    As it is now, you just count checking only against the months. Thus your function counts all Januaries for all Years, in stead of just for e.g. 2002.

    I would suggest putting a few columns alongside the filtered data:

    - OpenMonth with the formula =Month(E2)
    - CloseMonth with the formula =Month(F2)
    - OpenYear with the formula =Year(E2)
    - CloseYear with the formula =Year(F2)

    Now to have an updating chart:
    - Include those columns in the filter area
    - base the chart on the filtered area
    - change the filter criteria....

    Or alternatively, use a pivot chart (Data, pivot table and Pivotchart report)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    Could you be a little more specific? Maybe an example in my file sent back to me....??
    Thanks

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    I'm unable to attach files to messages.

    What I meant was:
    - add the columns I already suggested to the sheet that contains the source data
    - autofilter on all columns (including the new ones)
    - create a new chart using the columns in the source data sheet
    - set filter options and your chart should reflect them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    filtering is not the problem. I need to have the chart reflect numbers of orders opened and closed during any month / year. The chart gets that information from the table? on the summary page columns s,t,u. This information does not change when I use any filter. It maintains the information from the whole sheet.
    Attached is the file I am working with
    Attached Files Attached Files

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    OK. Here is the solution (I hope).

    Define this name (Insert, name, define):

    Name: Visible
    Refersto:
    =GET.CELL(17,INDIRECT("rc",False))+0*NOW()

    Now I added a column (J) to the right of your data called "Visible"
    in cell J2 I entered this formula:
    =Visible
    (copied down to match the rows of data)

    I changed the formulas in columns P and Q to:

    =IF(OR(E2=0,J2=0),0,MONTH(E2))
    =IF(OR(F2=0,J2=0),0,MONTH(F3))
    (copied down to match the rows of data)

    Now do a filter and see what happens to your chart.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    =GET.CELL(17,INDIRECT("rc",False))+0*NOW()

    Here I've used an old trick where ANcient XL4 macro functions are used in a defined name.
    The function GET.CELL(17,address) returns the rowheight of the cell denoted by address. (so in principle I should have named the formula RowHeight iso Visible)
    INDIRECT("rc",False) returns the address of the cell that contains the reference to the name. So if cell B1 contains =Visible, INDIRECT("rc",False) yields B1:
    =GET.CELL(17,B1) and thus one gets the rowheight of row 1, which is set to zero when the row is filtered out.

    The 0*Now() part is a trick to force the name to be recalculated whenever XL does a calculation.

    Note, that XL does not automatically recalc when you change filter settings. To force a recalc, place this formula anywhere convenient:
    =SUBTOTAL(1,A1:A2000)
    (this formula averages just the visible cells in the filtered range and forces a recalc on change of the filter)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    Please explain the logic in the formulae for Name, Visible) the value in J column is 15, is that correct?
    Thanks

  11. #11
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh chart (2002)

    Thanks much for your help. You're a genius. Everything works fine..

Posting Permissions

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