automatically create chart upon selecting

I have a spreadsheet containing about 50 different
financial ratios for 2002 thru the current month. I want
to be able to click on the name of the ratio and have a
chart automatically generate using all historical data
thru the current month, thus including each month as it
is added. Does anyone have any suggestions?

On Wed, 29 Oct 2003 07:43:30 -0800, Jason wrot=
e:

I have a spreadsheet containing about 50 different
financial ratios for 2002 thru the current month. I want
to be able to click on the name of the ratio and have a
chart automatically generate using all historical data
thru the current month, thus including each month as it
is added. Does anyone have any suggestions?

One way: create each of the charts using the dynamic charting technique=
=

described at =

http://www.geocities.com/jonpeltier/...x.html#hdrDyno and =

www.tushar-mehta.com, dynamic charts. Put those charts on a worksheet, =

not on their own sheet. Then hyperlink the appropriate cell to jump to =
a =

cell near the chart of interest.

I'm sure you can do some slicker things with VBA; this is only one way t=
o =

skin the proverbial cat.

-- =

Dave
dvt at psu dot edu

Yes, this is an option...one that I have already
considered. The problem with this is the ability to get
back to the original location I was at on the worksheet
containing the ratios. I know I could create another
hyperlink next to the chart to get back there, but this
seems like alot of work and could cause problems
maintaining all the links if the spreadsheet has
modifications. Ideally I just want the chart to pop up
without moving to a new location in the file.
-----Original Message-----
On Wed, 29 Oct 2003 07:43:30 -0800, Jason
wrote:

I have a spreadsheet containing about 50 different
financial ratios for 2002 thru the current month. I
want
to be able to click on the name of the ratio and have a
chart automatically generate using all historical data
thru the current month, thus including each month as it
is added. Does anyone have any suggestions?

One way: create each of the charts using the dynamic
charting technique
described at
http://www.geocities.com/jonpeltier/...harts/index.ht
ml#hdrDyno and
www.tushar-mehta.com, dynamic charts. Put those charts
on a worksheet,
not on their own sheet. Then hyperlink the appropriate
cell to jump to a
cell near the chart of interest.

I'm sure you can do some slicker things with VBA; this
is only one way to
skin the proverbial cat.

--
Dave
dvt at psu dot edu
.

On Wed, 29 Oct 2003 11:08:48 -0800, Jason wrot=
e:

Yes, this is an option...one that I have already
considered. The problem with this is the ability to get
back to the original location I was at on the worksheet
containing the ratios. I know I could create another
hyperlink next to the chart to get back there, but this
seems like alot of work and could cause problems
maintaining all the links if the spreadsheet has
modifications. Ideally I just want the chart to pop up
without moving to a new location in the file.

You can set up the chart to be dynamic; that takes care of one aspect of=
=

your question. Selecting which series to chart using a checkbox on the =

spreadsheet is the topic of Jon's earlier post in this newsgroup. I =

pasted it below. Hopefully this is a little closer to your wishes....

----snipped from Jon Peltier----
This is similar to the Chart by Checkbox example on my web site:

http://www.geocities.com/jonpeltier/...ByControl.html

In brief, there is a checkbox for each series that might be charted. Whe=
n =

each checkbox is checked, it runs a macro that redefines the source
data range. I put an undocumented workbook on my web site:

http://www.geocities.com/jonpeltier/...yCheckBox2.zip

The checkboxes are linked to cells in the sheet, which are named "boolY1=
", =

"boolY2", and "boolY3". The data for the chart is in ranges named
"theX", "theY1", "theY2", and "theY3". The macro checks each of the =

boolYi values, and if it's True, it uses Union to combine that theYi
range with theX and the other theYj ranges with True boolYj. Then it se=
ts =

the source data of the chart to this new combined range.

Someday I'll write a new page for this example.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Actually, I was going to suggest the combo box instead of checkboxes.
The chart can always be visible in the top of the worksheet.

BTW, Dave, thanks *a lot* for reminding me about this page I promised to
write. Can I borrow a cup of free time until next week?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

dvt wrote:
On Wed, 29 Oct 2003 11:08:48 -0800, Jason wrote:

Yes, this is an option...one that I have already
considered. The problem with this is the ability to get
back to the original location I was at on the worksheet
containing the ratios. I know I could create another
hyperlink next to the chart to get back there, but this
seems like alot of work and could cause problems
maintaining all the links if the spreadsheet has
modifications. Ideally I just want the chart to pop up
without moving to a new location in the file.


You can set up the chart to be dynamic; that takes care of one aspect of
your question. Selecting which series to chart using a checkbox on the
spreadsheet is the topic of Jon's earlier post in this newsgroup. I
pasted it below. Hopefully this is a little closer to your wishes....

----snipped from Jon Peltier----
This is similar to the Chart by Checkbox example on my web site:

http://www.geocities.com/jonpeltier/...ByControl.html

In brief, there is a checkbox for each series that might be charted.
When each checkbox is checked, it runs a macro that redefines the source
data range. I put an undocumented workbook on my web site:

http://www.geocities.com/jonpeltier/...yCheckBox2.zip

The checkboxes are linked to cells in the sheet, which are named
"boolY1", "boolY2", and "boolY3". The data for the chart is in ranges
named
"theX", "theY1", "theY2", and "theY3". The macro checks each of the
boolYi values, and if it's True, it uses Union to combine that theYi
range with theX and the other theYj ranges with True boolYj. Then it
sets the source data of the chart to this new combined range.

Someday I'll write a new page for this example.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

On Thu, 30 Oct 2003 10:36:00 -0500, Jon Peltier =

wrote:

BTW, Dave, thanks *a lot* for reminding me about this page I promised =
to =

write. Can I borrow a cup of free time until next week?

My pleasure. I wish I had a teaspoon of free time to loan you.

Dave

Thanks guys. The checkbox solution was just what I
needed. I am sure I will be able to find additional uses
for this.
-----Original Message-----
On Thu, 30 Oct 2003 10:36:00 -0500, Jon Peltier
wrote:

BTW, Dave, thanks *a lot* for reminding me about this
page I promised to
write. Can I borrow a cup of free time until next
week?

My pleasure. I wish I had a teaspoon of free time to
loan you.

Dave
.