I have a chart that references a range of cells that will change based on the selection made from a combo box. Each selection from the combo box will display a varying # of items to chart. I need help defining the chart range based on the output of that selection, a selection may have 5 items to chart or 25 items to chart.
Need Help With Creating a Dynamic Chart Range
- Thread starter gumbo191
- Start date
Check out how the SeriesLegend and SeriesName Named formulas are defined, and then you can put them into the chart by setting legend and series to:
='Fineline Graphs'!SeriesLegend
and
='Fineline Graphs'!SeriesData
PS. Since you have long names for each item, and this isn't a time-based chart, I turned it into a bar chart. IMO, it makes the labels much easier to read.
='Fineline Graphs'!SeriesLegend
and
='Fineline Graphs'!SeriesData
PS. Since you have long names for each item, and this isn't a time-based chart, I turned it into a bar chart. IMO, it makes the labels much easier to read.
NARAYANK991
Excel Ninja
Hi ,
I think the bar chart can have its axis reversed , so that the items with the highest sales appear on top.
See this file for the original column chart.
Narayan
I think the bar chart can have its axis reversed , so that the items with the highest sales appear on top.
See this file for the original column chart.
Narayan
If you select the Chart and select the Blue Columns you will notice that the formula bar now shows:
=SERIES(,'Copy of Sales Response Utilization 2 11 14.xlsx'!Chart_X_Axis,'Copy of Sales Response Utilization 2 11 14.xlsx'!Chart_Series,1)
This is saying that the series is charting the X Axis using a Named Formula called Chart_X_Axis and a Y Axis using Chart_Series
You can check these out in the Name Manager (Formula, Name Manager Tab)
You enter these in the Chart by Right click on the Columns and Select Data, Edit command
When entering the Named Formula you must include the sheet name
eg:
Use: ='Fineline graphs'!Chart_Series
Not: =Chart_Series
Excel will convert it to 'Copy of Sales Response Utilization 2 11 14.xlsx'!Chart_Series
But don't worry about that
=SERIES(,'Copy of Sales Response Utilization 2 11 14.xlsx'!Chart_X_Axis,'Copy of Sales Response Utilization 2 11 14.xlsx'!Chart_Series,1)
This is saying that the series is charting the X Axis using a Named Formula called Chart_X_Axis and a Y Axis using Chart_Series
You can check these out in the Name Manager (Formula, Name Manager Tab)
You enter these in the Chart by Right click on the Columns and Select Data, Edit command
When entering the Named Formula you must include the sheet name
eg:
Use: ='Fineline graphs'!Chart_Series
Not: =Chart_Series
Excel will convert it to 'Copy of Sales Response Utilization 2 11 14.xlsx'!Chart_Series
But don't worry about that
Correct as to how I created the ranges. To implement them, you click on the two Edit buttons in wizard (image shown above). For the series, I typed:
='Fineline Graphs'!SeriesData
and for Edit Horizontal Axis Labels:
='Fineline Graphs'!SeriesLegend
If you happen to go and look at them, you'll see that XL changes the formula to refer to the workbook, but IMO it's easier to type the sheet name than the workbook name.