Student improvements in a chart

I have a spreadsheet that tracks student performance before a course and after a course split over a number of different subjects. The spreadsheet takes the subjects from elsewhere and then puts them across the top columns of a new tab. in the 2 rows below it has the score out of 10 before the course and a score out of ten after the course for each subject.

The issue is that the number of subjects changes.

I've set up a bar chart that nicely shows the before and after for each subject but when it reaches a column that has no subject heading - and by definition no scores - it of course still puts the blank data in the chart.

I can change the range of cells that the chart draws upon but I was hoping for a solution where the chart would know not to show any bars for blank columns. Otherwise I have to manually adjust each time the number of subjects changes.

Be grateful for any thoughts!

Thanks in anticipation...

Ian

Have a look at the link below. Should help point you in the right direction.
http://peltiertech.com/Excel/Charts/...umnChart1.html

If you need more help then post a desensitized workbook and we can go from there.

BSB

Thanks very much BSB - very helpful.

I can begin to see how to do it but am confused as my data runs in columns rather than rows.

I attach a sample workbook as suggested in case there's an easy solution. Much obliged for any further help..

Is the data in rows 1:3 populated via formulas in your real data or is it manually input?

BSB

Hi

It's populated by reference to formulas in a different tab.

KR

Ian

Is that true for both the subjects (row 1) and the scores (rows 2 & 3)?
So if there were no scores they would show as zero?

Lots of questions I know, but the solution is very much dependent on representation of the actual workbook.

BSB

Yes, all the data that appears in the chart would be in cells on that tab that are populated by a formula that refers to cells on another tab. They won't show as zeros because I've unchecked the setting that says to show zeros.

that's very impressive!

I'm now trying to amend my original spreadsheet to do the same. I've set up the same Dynamic Named Ranges but I can't see how to set up a chart to refer to them. Your chart refers to the data in the correct cells but I don't know how it does it and hence how I can make mine do so...

On your workbook left click on one of the bars in the chart to select it and then look at the text in the formula bar. Should start with "=SERIES...."

Then do the same in the version I attached and see where it's different.

Yours will refer to a range by cell references but mine refers to the range using the named of the ranges I set up.
You simply need to amend this to reflect the required named ranges then repeat for the subsequent series in the charts.

BSB

That's great - thanks very much, BSB.

No probs at all Ian. Happy to help.

Thanks for the rep point and have a good rest of weekend

BSB