Problem formatting error bars when some values n the series are zero

Hi, this is my first post!

I am stumped. Is this just a bug in Excel that I have to work around? I have an interactive bar chart with formatted 'custom' error bars which function fine if there is a non-zero value for each element in the series, but whenever the series is sorted with a zero value at the top I get a catastrophic error and Excel bombs out. It would appear that the format of the line is the issue (I have set it to a dashed line). If I reset the format to a solid line there doesn't appear to be an issue. Has anyone else come across this?

Thanks

Bev

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

error bars.xlsm

Hi

I have attached an example chart, with two sort buttons and a button to format the error bars. With the error bars formatted as saved you can sort the data alpha or numeric. You can apply the dashed format to the error bars if the data is sorted so that the first value in non-zero (choose sort alpha) but as soon as the data is sorted numeric Excel crashes and attempts to recover the document.

Bev

Hi Andy

I suspect that this is the problem, as I've never had this problem on my home pc running Excel 2007, just like in the office. But I've checked the versions and my home edition has a newer version number (12.0.6683.5002) than my office version (12.0.6611.1000). Could this be a reason? Unfortunately the workbooks are part of a publication and so, rather than risk crashing a user's pc (and rendering it useless for five minutes whilst Excel tries to recover the workbook) I've used a 'safe' format in the chart. I don't have any control over the application of service packs at work, but how do you find out what's been 'fixed' in later releases?

IT have been promising upgrades to Office for ages but I'm not holding my breath! And anyway, I've just about sussed 2007...

Bev

Thanks Andy, I'll have a look at this and see what our IT have to say.