Charting only select cells based on value from other column

[AnnR]

I'm developing a project dashboard with the following data:


Column B - ID

C - Project Name

G - Budget

H - Spent

M - Budget Source


I have dozens of projects - some with budgets, and others without. I need to create a chart that shows the Budget vs. Spent data for only those projects that have a budget source listed in column M.


In addition, multiple projects may have the same budget source, so I'd need to SUM those prior to entering them into the chart. I've used a SUMIFS function to get that to work, but I don't know how to pull the data from the Budget/Spend columns to create my bar chart. I'd love it if one of you fantastic excel ninjas could help me!


Many thanks!


Ann

 

[Sajan]

Hello Ann,

It sounds like you are looking to conditionally plot a data series (that might be the result of a sum of similar rows). If so, try the following approach:


To be able to plot data series dynamically, you will need to utilize Named formulas as the source data for the chart.


The following is one approach for dynamically plotting various data series.


Assuming that your budget data range is named "Budget", your spend data is named "Spent", and your budget source data is named "BudgetSource", the following formulas would create the data for the chart.


=INDEX(BudgetSource, MATCH(1, ISNA(MATCH(BudgetSource, A$15:A15,0))*(BudgetSource<>""),0))

enter with Ctrl + Shift + Enter


=SUMIFS(Budget,BudgetSource,A16)


=SUMIFS(Spent,BudgetSource,A16)


Now that the processed data is setup, we now need to create some named formulas to use as data sources for the chart.


BudgetSourceDataForChart

=Sheet2!$A$16:INDEX(Sheet2!$A:$A, MATCH("ZZZZZZZZ",Sheet2!$A:$A ))


BudgetDataForChart

=Sheet2!$B$16:INDEX(Sheet2!$B:$B, MATCH("ZZZZZZZZ",Sheet2!$A:$A ))


SpentDataForChart

=Sheet2!$C$16:INDEX(Sheet2!$C:$C, MATCH("ZZZZZZZZ",Sheet2!$A:$A ))


Now insert a bar chart, and set the data series as follows:

Add the following as Y-axis series

SeriesName ="Budget"

SeriesValues =Sheet2!BudgetDataForChart


SeriesName ="Spend"

SeriesValues =Sheet2!SpentDataForChart


Add the following as X-axis series

AxisLabelRange = Sheet2!BudgetSourceDataForChart


That should be it. As you make changes to your budget source data, the chart should add or remove data series as appropriate.


You can see this approach in action in the following workbook:

http://speedy.sh/AG2Mh/Chandoo-Dynamic-Charting-of-data-series.xlsx


Cheers,

Sajan.