Using named data ranges in charts (Excel 2007)

**Sam_Gregson** · 03-09-2009, 01:06 PM

Dear all I hope you can help me - because my boss will eat me tomorrow if you can't!!!

I am having a bit of trouble in Excel 2007 using named data ranges. I have attached a small file to explain the problem - i expect one of you will be able to solve the problem very quickly and easily.

Basically, I want to plot the graph included on the right (which i plotted manually) using the named data labels which I have created (see name manager). However, I do not seem to be able to make any progress.

The named ranges are intended to plot all the values in the Power output bins
column and the corresponding values in the Values for plotting column. The number of points that need to be plotted changes as the user alters the parameters in the top left hand corner - therefore I need to resort to named data lables/ranges. However, I am having the following problems:

1) The named labels do not seem to work/select the correct ranges

2) I am not sure how I make the graphs plot the named/labelled data ranges - do I have to use a special syntax in the chart select data section? Could someone provide me with a list of idot proof steps?

If anyone out there could help me I would be extremely grateful!!! I really need to get this done tonight, so a speedy response would be even more welcome.

Thank you so much in advance,

Sam

**Andy Pope** · 03-09-2009, 01:17 PM

Start by changing the formula used in BIN_LABEL. You currently have the column width at zero where as it should be 1.

=OFFSET(Sheet1!$D$10,0,0,((Sheet1!$C$4-Sheet1!$C$5)/Sheet1!$C$6)+3,1)

You can then select the series and modify the series formula.

=SERIES(,'Problem - named ranges.xlsx'!BIN_LABEL,'Problem - named ranges.xlsx'!VALUEFORPLOTTING_LABEL,1)