Trying to specify a range name in a chart series

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Well-known Member
Joined
Aug 31, 2006
Messages
585
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Well-known Member
Joined
Aug 31, 2006
Messages
585
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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".
Code:
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.
 

Forum statistics

Threads
1,136,650
Messages
5,677,000
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top