Solved: Chart to only use visible data

[malik641]

First off, I'm kinda new to Chart creating....with formulas. I know how to make Charts of different types and how to add data and linked cells for titles, etc...

But what I'm looking to do I have a feeling may not be done so easily...

Here's the premise:
I have 3 sheets: 'Forecast', 'Actual', and 'Results (Days Late)'

Forecast sheet:
Has projected dates for numerous headings (Sample Receipt Date....Repeat Analysis date....etc)

Actual sheet:
Contains the actual dates for the corresponding projected dates.

Results (Days Late):
If you haven't figured it out, this sheet will display the ending results from the two dates. If the date was on-time, it will display "On-Time". And if it was late, it will have this format:

Projected: (Proj. Date)
X Years Y Months Z Days

*The "X Years Y Months Z Days" piece of the formula is just how much time was it late.

This sheet uses an advanced filter to collect Late records under certain headings e.g.:

A2=(Optional)
A3=Sponsor
A4=This_Sponsor

B2=Criteria Heading & Date
B3=This_Heading
B4=*2004*

This example will look for 2004 in cells under "This_Heading" with "This_Sponsor" and show data (if any...obviously)



So now that you have an idea of how my workbook is...Can I make a chart that will use filtered data (visible cells, of course)?

I figure that each heading will have its own graph and the Y-Axis will have an integer value (occurences late given filtered criteria) and linked cells that display what was filtered. This would be an bar graph and the criteria for the formula (for Y data points) would find cells (under the heading) that are NOT blank and do NOT say "On-Time"....a SUMIF function should do the trick.

I'm trying NOT to use VBA for this, BTW. There will be so much data to use that I feel that VBA may slow down calculation significantly.

[vonpookie]

Don't know if it'll work for you, but under Tools\Options\Chart tab there is an option for "Plot visible cells only".

I believe that will affect all charts, though.

[JonPeltier]

Plot Visible Cells Only is on by default. It is only enabled in the dialog if a chart is active, and the setting applies only to the active chart.

[malik641]

Thanks for the info VonPookie and JonPeltier Didn't know Excel did that.

I'm marking this thread solved, but expect me to be back with more Charting questions

Again Thanks!

[malik641]

Don't know if it'll work for you, but under Tools\Options\Chart tab there is an option for "Plot visible cells only".

I believe that will affect all charts, though.

Nah, it only affects the active chart. According to John Walkenbach, anyway.

[JonPeltier]

Nah, it only affects the active chart. According to John Walkenbach, anyway.

I'm surprised nobody tries these things out themselves. I don't waste time knowing the answers to small details like this, I just test for them whenever I need to. This one took 30 seconds to make a dummy range, filter out half the rows, make two charts, and change the setting on one of them. Instant answer.

[Zack Barresse]

I think it's because most people are either 1) scared of charts or 2) have not messed with charts enough to be comfortable testing this, because they're scared of charts. That's why you're so famous Jon, because you wander into the deepest, darkest hole in Excel you can find. I think a lot of people would rather do pivot tables than mess with charts. LOL!

Jon, do you know if such a feature (as Joseph is asking) has been enhanced any in the new Office? I sure hope the new version has chart improvements. I have read some, but haven't seen much on charting.

[JonPeltier]

Hi Zack -

I think it's because most people are either 1) scared of charts or 2) have not messed with charts enough to be comfortable testing this, because they're scared of charts. That's why you're so famous Jon, because you wander into the deepest, darkest hole in Excel you can find. I think a lot of people would rather do pivot tables than mess with charts. LOL!

I didn't mean this just about charts. A lot of posts in online forums are really just "I want to know if I can select that cell and make it bold" kind of posts, and I'm only slightly exaggerating. Just try it and see.

The more interesting posts are from people who have tried something, and it works differently than expected, and they are asking why or why not, and wanting to go beyond where they are. Sometimes that requires a jump into something different, and those are the ones they no longer have the ability to "Just try it," but I know a few more tricks, so I can just try it for them, and learn something for myself in the meantime.

Jon, do you know if such a feature (as Joseph is asking) has been enhanced any in the new Office? I sure hope the new version has chart improvements. I have read some, but haven't seen much on charting.

Hmmm, well, I'm under NDA, so I can't say too much. In David Gainer's blog yesterday, he described a small amount about charting developments (http://blogs.msdn.com/excel/default.aspx). So far most of the published commentary about charts is "Wow! they sure look nice, don't they?" There's a new rendering engine for charts and other graphics, and the screenshots look nice. But as the comment from Hadley says "The new graphics look absolutely beautiful, but what efforts are you making to help people make useful graphics?" By the time the NDA restrictions have been lifted, I may have learned enough to comment intelligently.