Trying to specify a range name in a chart series

[Mr930]

How would I specify a named range as the Y series in a chart? Every time I type the name in various ways I get an unhelpful error. Is it possible to use a named range in this manner?

thanks
Fred

 

[Datsmart]

You need to include the name of the workbook.
Example:
='MyWorkbookName'!RangeName
The workbook name must be surounded by single quotes and followed by an exclamation point, then your named range.

 

[Mr930]

I copy this file a lot so how would I be able to use this option and not have subsequent files refer back to the original?

thanks
Fred

 

[Datsmart]

Your question of, "not have subsequent files refer back to the original?", tells me you might be copying your chart. When you do that, it still refers back to the original table of data.
Couple of suggestions:
You can use code to rebuild the chart entirely like Greg Truby shows here:
http://www.mrexcel.com/board2/viewtopic.php?t=213900
-or-
You can use just portions of his code to assign new ranges to your existing chart.
On the following site are instructions on how to setup a Dynamic Chart that adjusts to changing ranges.
http://peltiertech.com/Excel/Charts/Dynamics.html

Hope this helps.

 

[Mr930]

I will be copying the whole workbook (.xls file) since I will change my data but still be using the same spreadsheet. I am worried if I need to hard code the workbook name into my chart range name, that it will not be correct once I copy the .xls to a new name. WOn't the chart still refer back to the orignal range name? I was thinking I could use the ActiveWorkbook property somehow in my chart but that gives me an error.

thanks
Fred

 

[Datsmart]

You can assign the active workbook name to a variable and use the variable in your code.
This code will reassign the third series of Chart # 7 to a named range called "Data3".

Sub Macro1()
' Create variable for Workbook Name
    MyWBName = ActiveWorkbook.Name
    'input single quotes and Exclamation mark around wb name
    MyWBName2 = "='" & MyWBName & "'!"
' Assign variable to third series of chart number 7
    ActiveSheet.ChartObjects("Chart 7").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(3).Values = MyWBName2 & "Data3"
    ActiveWindow.Visible = False
    Windows(MyWBName).Activate
    Range("A1").Select
End Sub

Change "Chart 7" to whatever your chart number is.
Change "Data3" to whatever name your range is.
Add code to the macro to setup each of your chart Series to the current workbook.
Be sure to change the number in, "SeriesCollection(3)" for each series you are working with.

Hope you can use this.