CODEWITHSUNDEEP
excelreferencerangecell
Max
Max

Reputation: 13334

How to use cell values to define the range of chart data?

I have a set of 500 cells from which I've created a chart using the following range definition:

=Sheet1!$A$1:$A$500

I have 2 cells B1 and B2 in which I would like to define start and end values (inside 1-500) for the chart to show only a subset of my data (let's say from 20 to 200).

How can I reference the cells B1 and B2 to replace $1 and $500 in my data range definition?

Upvotes: 3

Views: 27047

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

If I have understood your query correctly then You can use .SetSourceData and adding the values from B1 and B2

Here is an example

Option Explicit

'~~> Please amend the code as applicable
Sub Sample()
    Dim objChrt As ChartObject
    Dim chrt As Chart
    Dim StartVal As Long, endVal As Long

    With Sheets("Sheet1")
        StartVal = .Range("B1").Value
        endVal = .Range("B2").Value

        Set objChrt = .ChartObjects("Chart 1")
        Set chrt = objChrt.Chart

        chrt.SetSourceData (.Range("A" & StartVal & ":A" & endVal))

    End With
End Sub

Screenshot

enter image description here

Upvotes: 3

Peter L.
Peter L.

Reputation: 7304

In general, you should use dynamic charts with calculated named ranges with help of OFFSET and COUNTA used in SERIES function that defines chart series.

It's pretty much to explain here, but here is my own sample file created for staff training: https://www.dropbox.com/s/5enub45nn4y36gz/DynamicCharts4Types.xlsx

It includes 4 different types of applied dynamics, such as show last X points, show points from X to Y, etc. To see the magic go to Ribbon Formulas > Name Manager and check how different ranges are defined.

Upvotes: 10

Related Questions