Refresh chart (2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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)

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

=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)

Reply | Quote

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

Reply | Quote