Update a chart using Hyperlinks
 

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

Update a chart using Hyperlinks
 

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Access Joe" wrote in message
...
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

Update a chart using Hyperlinks
 

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" wrote:

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Access Joe" wrote in message
...
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

Update a chart using Hyperlinks
 

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Access Joe" wrote in message
...
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" wrote:

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Access Joe" wrote in message
...
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

Update a chart using Hyperlinks
 

Thanks Andy. I'll give this a try!

"Andy Pope" wrote:

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Access Joe" wrote in message
...
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" wrote:

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
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Access Joe" wrote in message
...
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