Help with charting using Named ranges

**Dial1** · 05-03-2013, 12:46 PM

I do not remember this being so difficult in 2003. ( I am using 2010)

I am trying to create a simple line chart but using named ranges. Everytime I try to load the named range (using F3 and selecting the name) I get an error. I have attached a simple example if someone can help me out.

My ultimate goal is to have two different defined ranges, and using being able to reference a single cell in the workbook that contains the named range which would change based on the value of a data validation box. But I thought I would crawl before I walk as I can't even get a simple chart created using named ranges.

Thanks for the help.

**K m** · 05-03-2013, 01:23 PM

There are certain "Reserved" names. Excel may not like Named ranges named Date, Data or Series

Try different names

**Dial1** · 05-03-2013, 01:35 PM

Question on the namded ranges. Do I have to name each data series to an individual name? ie Do I have to name "Gold" and its data point to one name, and "Blue" and its data point to another name? Or can I group them together and either name all of the data, or both the "Gold" and "Blue" and their data to one name? I am building 15 charts so if I had to name each individual Series name I may look for another rought as that would be 100+ named ranges.

**K m** · 05-03-2013, 01:48 PM

Depends on what you want.

If you want to plot Gold (B4:H4). I would name the range B4:H4 as Gold
If you want to plot Blue (B5:H5). I would name the range B5:H5 as Blue

What do you want to do with the data?

**Dial1** · 05-03-2013, 01:54 PM

That is what I want to do. I would have like to create dynamic ranges but it looks like I would have to create WAY too many named ranges. In the example I provided I would have to create 3 named ranges. one for Gold, Blue and the date range correct?

Seperate questions. In excel 2003 I thought I could just F3 and click the name I want to use as the range. However, I 2010 it appears that I need to put a worksheet/workbook qualifier infront of the named range??? ie example.xlxs!Gold is that correct? I can't seem to find the correct path name on my actual working file. I could do it fine in the example file but using the above but the same is not working on my large working file.

**K m** · 05-03-2013, 02:07 PM

Excel 2010 adds the file information for you even though you may have named the range GOLD.

BTW: An easy way to name a range is to highlight the area with a mouse and go to the area in the upper left hand corner just below the tool bar and type in a name. That "name" is now the name of the highlighted range

BTW: You don't have to use range names for charting. You could use OFFSET or INDIRECT to make dynamic charting

I have added a dynamic charting example using the OFFSET function

**Dial1** · 05-03-2013, 02:16 PM

I am not seeing where you used offset other than in the named range?? Am I missing something?

**Dial1** · 05-03-2013, 02:20 PM

Capture.PNG

Attached is the error I get if I just try to input the named range in the series value. It will only allow my to use the name if it is proceeded by the file name ext.

**K m** · 05-04-2013, 06:30 AM

To make the chart dynamic in both x and y ranges, you need both x and y to include the OFFSET function for each range you want graphed.

I think the example I sent you had a static x and dynamic y.

If you wanted to graph multiple ranges, each range would have two OFFSET ranges, one each for the x and y.

Still confused?