How to get a chart to use a data address that is in a cell... I.E. (indirect function)

Hello,

I have run into this issues several times and always have to go a much longer route, but i'd figure i'd post the topic and see if there is a simple solution to the issue.

First I'm dealing with a data source that provides dates and numbers in an order that is conforming to the way a graph picks up and auto creates a graph.

for example...


Column A Column C
6/4/2014 185.62
6/3/2014 185.77
6/2/2014 186.32
5/30/2014 183.9
5/29/2014 185.28
5/28/2014 185.61
5/27/2014 187.03
5/26/2014 188.17
5/23/2014 188.53
5/22/2014 189.38
5/21/2014 185.78

*There is never anything in column B
*In addition there are 90 to 1000 rows of dates. (for this example lets just stick with 90 row max)
*In my spreadsheet there are 200 sets of these data sets. Essentialy going from column A to Column VR
*This has to be completely automated..FYI

I've created a formula that will take an entry from a VBA based text box from a user and output a code to the worksheet. This code Id's the data that the workbook needs to know. The worksheet then inturn goes and outputs the full address of the data set upon which it wants to build the graph.

Ultimatly I'm left with a cell that has Address!A5:B69 as the address for the graph.

THE QUESTION: How can I get a graph that will use this Address!A5:B69 <----(cell data provided from a vlookup) as the data input for a graph. Also as the user selects different information the graph needs to updata and change. so i figure some kind of dynamic name range for the solution requested.

Please let me know if clarification is needed. I hope my explanation is clear.

VBA Code preferred.. Please explain code if its VBA!


here is my code. and i'm getting an error at .setsourcedata = Rgn1 because its in ""quotes i believe. Since i'm pulling the address from a cell and its taking it in as text

Sub Test()
Dim LastRow As Long
Dim Rng1 As Variant
Dim Rng2 As Variant
Dim Rng3 As Variant
Dim Graph As Range
Dim L1 As Range
Dim L2 As Long
Dim SheetName As String

' Sheets("BloomRaw").Activate
' With ActiveSheet
' LastRow = "69"
' Set Rng1 = .Range("A6:A" & LastRow & ", B6:B" & LastRow)
'
' 'Set Rng1 = .Range("X4")
' End With
Rng1 = Sheets("Address").Range("AB4").Value

SheetName = Sheets("Address").Name

Charts.Add
With ActiveChart
.ChartType = xlLine
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=Address!AA4"
ActiveChart.SeriesCollection(1).XValues = "=Address!AC4"
ActiveChart.SeriesCollection(1).Values = "=Address!AD4"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=Address!AA5"
ActiveChart.SeriesCollection(2).XValues = "=Address!AC5"
ActiveChart.SeriesCollection(2).Values = "=Address!AD5"

.HasTitle = True
.ChartTitle.Text = "OAS"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Option Adjuest Spread"
.SetSourceData Source:=Rng1
.Location Where:=xlLocationAsObject, Name:=SheetName
With ActiveChart.Parent
.Height = 170 ' resize
.Width = 270 ' resize
.Top = 175 ' reposition
.Left = 1270 ' reposition
End With


End With

End Sub