CODEWITHSUNDEEP
excelvba
PetePwC
PetePwC

Reputation: 37

Chart range based on the value in a cell

I need to create a line chart that selects a range of data based on the value in a cell. For instance, in cell C1 I write A1:B4, this means the chart is a display of the data in cells A1 to B4. If I simply change the value in cell C1 to A1:B9, I want the chart to display the data of this range - you get the point. This shouldn't be too hard, but i'm not getting it right (and for some reason the web is full of the same examples that do not apply for my)

I've tried using a Named Range function. I still think this is the way to go, but I need some help.

Upvotes: 0

Views: 3384

Answers (2)

Eddie Hodges
Eddie Hodges

Reputation: 1

Super easy solution without using VBA. This works if you have dates for your x-axis. Use UNIQUE and FILTER to populate the values needed for your x-axis.

Chart using unique axis

Chart using original axis

In this example, the original x-axis values are in F9:F25. The data values are in G9:G25. In K9, I am using the formula UNIQUE(FILTER(F9:F25,G9:G25<>"")), which results in showing the dates for only the data values that are not empty. Even though the x-axis range is from K9:K25, the chart will stop at the last value in column K.

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61975

There is no VBA needed for this.

Let's start having the following worksheet named Sheet1:

enter image description here

Now we need three named ranges. One for the whole range which we get indirect form C1, one for the categories which is the left column of the whole range and one for the values which is the right column of the whole range.

So in name manager we create following named ranges:

enter image description here

Note all named ranges are in scope of the sheet Sheet1 and not in workbook scope. So while creating the named ranges, always choose scope Sheet1 instead of Workbook

Name myRange refers to =INDIRECT(Sheet1!$C$1). So it gets it's range from that cell value.

Name myCategories refers to =INDEX(Sheet1!myRange,,1). That gets all rows (since no special row is given) from column 1 of myRange.

Name myValues refers to =INDEX(Sheet1!myRange,,2). That gets all rows (since no special row is given) from column 2 of myRange.

Now we can insert a chart (a pie chart for example).

Then we right-click the chart, and then choose Select Data.

First we delete all present series on left side below Legend Entries (Series), if any. Then we add a new series. In Series values: we put the formula =Sheet1!myValues, OK.

On right side below Horizontal (Category) Axis Labels we click Edit and put in the formula =Sheet1!myCategories, OK.

Then OK for the whole select-data-dialog.

Now if we change the cell value of C1 into something what INDIRECT can interpret as a cell range, then the chart will change too.

To give a VBA solution also:

Let's have the same sheet as above. Data in A1:B8 and range address in C1.

Now create the wanted chart. It must be the one and only chart object in that sheet.

Now put the following code in sheet module of Sheet1 (right click on the sheet tab and click View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim oChartObject As ChartObject
 Dim oChart As Chart
 If Target.Row = 1 And Target.Column = 3 Then
  On Error Resume Next
  Set oChartObject = Me.ChartObjects(1)
  Set oChart = oChartObject.Chart
  oChart.SetSourceData Source:=Me.Range(Target.Value)
  On Error GoTo 0
 End If
End Sub

This code leads to changing of source data of the chart if the value of C1 (row 1, column 3) changes.

Upvotes: 2

Related Questions