Update a chart using Hyperlinks

[Access Joe]

Hi all: PC Excel 2003

I have a Summary sheet that lists a bunch of drug names in Column A. To the
right of that column is one big Chart (just one). Now what I'm looking for
is a way to create some kind of "hyperlink Macro" so that when I click on a
specific Drug name in Column A, the big chart on the right will show data for
that drug.

Remember this is a summary sheet. All the data for each drug is spread out
across multiple worksheets...to which i could easily create charts on each
one if I wanted to. But I don't want someone to go TO those worksheets. I
want them to remain on the Summary Sheet, click the desired drug in Column A,
and see the chart for that selected drug right there on the same summary
sheet.

Hope this makes sense. Can anyone help?
Joe

 

[Andy Pope]

Hi,

You could use the Worksheet_SelectionChange event to update the summary
chart data.

If your chart data is located in the same place on various sheets then a
INDIRECT formula could be used.
Otherwise use use code to copy values.

Cheers
Andy

 

[Access Joe]

Thanks Andy. Would you be able to tell me how to go about using the
Worksheet_SelectionChange event? Unfortunately, the data ranges would always
be different on each worksheet...and I'm not familiar with writing code for
that.

Any more details you could provide would be most helpful.

 

[Andy Pope]

Assuming Sheet1 contains a chart and the range A1:A3 contains 3 descriptions
of the data you want to display.
Select a cell will cause the routine to run. If that cell is in the range
A1:A3 then the chart source data will be changed.

You will need to update the references for the charts data source to suit.

'----------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Range("A1:A3"), Target) Is Nothing Then

Select Case Target.Address
Case "$A$1"
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet1").Range("N17:O19"), PlotBy:=xlColumns
Case "$A$2"
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet2").Range("F6:G11"), PlotBy:=xlColumns
Case "$A$3"
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet3").Range("B28:C34"), PlotBy:=xlColumns
End Select
End If

End Sub
'------------------

Cheers
Andy

 

[Access Joe]

Thanks Andy. I'll give this a try!

Assuming Sheet1 contains a chart and the range A1:A3 contains 3 descriptions
of the data you want to display.
Select a cell will cause the routine to run. If that cell is in the range
A1:A3 then the chart source data will be changed.

You will need to update the references for the charts data source to suit.

'----------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Range("A1:A3"), Target) Is Nothing Then

Select Case Target.Address
Case "$A$1"
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet1").Range("N17:O19"), PlotBy:=xlColumns
Case "$A$2"
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet2").Range("F6:G11"), PlotBy:=xlColumns
Case "$A$3"
ActiveSheet.ChartObjects(1).Chart.SetSourceData _
Source:=Sheets("Sheet3").Range("B28:C34"), PlotBy:=xlColumns
End Select
End If

End Sub
'------------------

Cheers
Andy