Excel - multiple charts from one data set, each using different criteria/rows

I have some charts (attached file) that read a table.  The first one is the most basic -- all of the rows.  The others display a subset of the data (based on criteria in the first column).  The cells were selected manually, so the results are unsorted.  That doesn't work (and the data will not always be in that position so cells have to be selected manually every time).
The goal is to have each chart with bars sorted, reading the table, with the correct rows selected (for East, West, South, etc).
The only method I can think of is to create a separate table for each chart, with each table having the correct data, sorted separately.  That does work.  But if there were 15 charts, that would be 15 tables -- all extracting subsets from the same data.
Is it possible to feed a query result to each chart with an IF statement of some kind?
SELECT Operation, 2009 YTD WHERE Region = "East"

I have tried a VLOOKUP, which also would require separate rows of data for each chart -- but I couldn't get that to work either.
Any ideas?
Chart-criteria.xls
LVL 2
billb1057Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
If your data are always sorted properly we could use dynamic Names to power the separate charts, but that can get very messy.

I prefer using a PivotTable with PivotChart.

The attached workbook has a PivotChart with Region as a page field, allowing you to quickly switch Regions if you wish.

Patrick



Q-25784385.xls
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello billb,

this is a classic case for a pivot chart. Select from the filters what you want to show. Create more than one pivot chart to show different aspects at once.

see attached

cheers, teylyn

Copy-of-Chart-criteria.xls
0
Patrick MatthewsCommented:
I see teylyn and I have some sort of mind meld going on :)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Teheehee.  Better than a mind melt. Off to catch ZZzzz. No Excel solutions after 1:30 am.
0
sgvillCommented:
If you don't need to see all the charts at once, you can create a pivot chart.  To do this, select your data and insert pivot chart.    Put the region as your filter, Operation as your Row Label and Sum of 2009YTD as your values.   This gives you a sorted bar chart for all, which you can filter by region (sorted).
0
sgvillCommented:
oops, i should hit refresh before i answer :)
0
billb1057Author Commented:
Two good solutions -- posted within one second of each other.  Maybe too close to call?
Adding points here -- and a follow up:
Is it possible to format a Pivot Chart so that it looks like a regular chart -- in other words, minus all the extra buttons and drop downs, etc.?
0
billb1057Author Commented:
Important point, sqvill -- yes, I do need to see all the charts at once, so Pivot Charts really wouldn't work in this case, right?
0
billb1057Author Commented:
Or could it just be a matter of creating several pivot tables ... but that's sort of like the first option I mentioned with a separate table for each chart.  ????
0
sgvillCommented:
you can click on the chart and "move it" to its own worksheet.
0
billb1057Author Commented:
??? I'm not following that.
I created an additional chart on Teylyn's file but when the filter is changed to a different region, it changes both charts to the same region.
Is there a way to do it so each chart shows different criteria on the same page?
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
As I already said in my comment above: You can have several pivot charts on one worksheet. You don't have to show a pivot chart on a chart sheet. Each pivot chart requires its own pivot table, but with a data set like this it's very easy to set up.

As an alternative, you can create each pivot chart in its own worksheet (not a chart sheet, but a regular worksheet) and then use the camera tool to combine dynamic images of these several charts into a dashboard.

cheers, teylyn
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Going the Camera Tool/Dashboard approach, you can possibly avoid showing the selection buttons, too.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
In the attached file, the first sheet has the pivot tables, Sheet2 has the pivot charts and Sheet3 has dynamic images of the charts, cropped to omit the buttons. These can be arranged to suit.

cheers, teylyn
Copy-of-Chart-criteria.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
billb1057Author Commented:
I guess I was looking for  a VBA solution where you might be able to query the data and feed different results by a variable to each chart (without having to actually populate new spreadsheet cells with data).
I will trust the Experts here and conclude that there's no easy way to do it.  Pivot charts have some advantages, but they still require separate Pivot Tables.  It's not that much different than my original solution which is a separate table for each chart.
Part of my question was just testing to see if there was a better way -- so, it's valuable to know what solutions others have come up with, even if it doesn't really solve the problem (of having to create separate data sets for each chart -- whether with Pivot table or regular table).
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>>??? I'm not following that.

If you are using Excel 2003, it might be best to create separate sheets for each pivot table and pivot chart. Place the Pivot chart on the same Sheet as the pivot table it belongs to and size it to suit. Then use the camera tool to create a dashboard with all chart images arranged the way you need them.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Billb, each chart will need its own table, or data stored somewhere in a table. Even if you feed the table from a query, you need to put them into Excel cells first, before you can use them to create Excel Charts, Pivot or not.

cheers, teylyn
0
billb1057Author Commented:
Your last version was very good, Teylyn.   It looks like both charts are reading from the same Pivot Table -- why does that version work and the others didn't?  Is that because they're from the Camera Tool or dynamically pasted in somehow -- or is it a function of how you built the original Pivot Table?  Either way, it looks great.
Thanks
0
Patrick MatthewsCommented:
I like teylyn's idea of using the Camera tool.  harfang has a terrific description of it here: http://www.experts-exchange.com/articles/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Excel-Tips-Tricks-The-Camera-Tool.html

Another approach would be to sort your data by region then operation, and create a set of paired dynamic Names.

For example, I created Central_Labels to refer to:

=INDEX(Data!$B:$B,MATCH("Central",Data!$A:$A,0),1):INDEX(Data!$B:$B,MATCH("Central",Data!$A:$A,0)+COUNTIF(Data!$A:$A,"Central")-1,1)

and Central_Values to refer to:

=INDEX(Data!$C:$C,MATCH("Central",Data!$A:$A,0),1):INDEX(Data!$C:$C,MATCH("Central",Data!$A:$A,0)+COUNTIF(Data!$A:$A,"Central")-1,1)

and then repeat that for the other regions, updating the region name as you go.

In the attached workbook, I then added a worksheet (Dashboard) with five charts, with each chart using those dynamic Names as the source for its values and labels.


Q-25784385.xls
0
billb1057Author Commented:
Matthew -- that is a great looking concept.  You mentioned earlier that this method could be messy, so I'd be careful about that, but I will play around with this.  Thanks!
 
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> Your last version was very good, Teylyn.   It looks like both charts are reading from the same Pivot Table

They're reading from different pivot tables.  

Pivot 1 in J2:L11
Pivot 2 in N2:P30 on the first sheet.
0
billb1057Author Commented:
Right, I missed that.  How do you determine which table the chart is reading from?  I normally look for the Source data, but that isn't an option with Pivot Charts.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
If you click the chart series, the range is displayed in the Series formula in the formula bar. Thanks for the grade.

cheers, teylyn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.