Using dynamic Ranges

[StanG]

Okay, I've been beating my head on this for a while now.
I'm trying to create a chart where a user enters 3 items
of data, and then using a range created from lookups,
update the displayed data.

The formula I'm using to create the range looks like:

="CN!$"&VLOOKUP(B3,A17:C65,2)&"$"&TEXT(B4,0)&":CN!
$"&VLOOKUP(B3,A17:C65,3)&"$"&TEXT(B5,0)

and gives me a result like:

CN!$K$51:CN!$N$100

Now is there anyway to use this as a data range for a
chart without updating the chart each time the range
changes?

TIA
Stan

 

[Jon Peltier]

Stan -

You can use dynamic ranges in the definitions of the X and Y values and
Name of a charted series, but not for the entire source data range of a
chart. But you can use this in VBA to define your chart range, using a
worksheet event (like the worksheet_change or worksheet_calculate events).

Suppose the formula you've posted is in cell A1. Right click on the
sheet tab, select View Code, and look at the code module that pops up.
You have an empty frame of the Worksheet_SelectionChange event. You can
delete it or ignore it.

There are two pulldowns at the top. Worksheet is still selected in the
left one, so leave it alone. Select Change from the right one, and this
new empty procedure appears:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

all you need to do is enter a few lines of code inside:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With ActiveSheet.ChartObjects(1).Chart
.SetSourceData Source:=Range(ActiveSheet.Range("A1").Value)
End With
End Sub

Now when the sheet changes, the chart source data range is updated.

- Jon

 

[StanG]

That works slick - never thought to use VBA.

Thanks Jon

 

[Alex A]

Jon, can you elaborate on how the SetSourceData works.
I was able to temporarily solve my problem (from the post
directly below) with that command with the following code

'Update Graph2
With ActiveSheet
Set MyXValues = .Range(.Cells(x1, 1).Address, .Cells(x2,
1).Address)

Set MyYValues = .Range(.Cells(x1, 5).Address, .Cells(x2,
5).Address)
End With

Sheets("Chart2").Select
ActiveChart.SetSourceData MyYValues, MyXValues


I could not get that other method to produce reliable
results because when the variables would change sometimes
that graph would error out. But this seems to be
working. Im not sure if I used it correctly though? Is
the "SetSourceData MyYValues, MyXValues" taking the Y
series data first and then the X series data after the
comma? It pops up as "plot by" but I'm not sure what
that means.

Please advise
Alex.

 

[Jon Peltier]

Alex -

According to the help files:
-----
SetSourceData Method

Sets the source data range for the chart.

Syntax
expression.SetSourceData(Source, PlotBy)

expression Required. An expression that returns a Chart object.

Source Required Range. The range that contains the source data.

PlotBy Optional Variant. Specifies the way the data is to be plotted.
Can be either of the following XlRowCol constants: xlColumns or xlRows.
-----

SetSourceData selects the entire data range for the chart, like manually
choosing Source Data from the Chart menu and clicking on the Data Range
tab. PlotBy is whether the series are in columns or rows. SetSourceData
makes certain assumptions about the data. If you don't specify PlotBy,
it will default to columns, unless there are more columns than rows in
the range. If the top left cell is blank, it will use the top row for
series names, the left row for categories, and the rest of the columns
for series data. If the top left cell isn't blank, it depends on the
chart type: in a scatter chart or if the first column is text, the first
column becomes the X values (categories), otherwise, all columns are Y
values for series.

- Jon