automatically create chart upon selecting

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

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?
 
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?
Click to expand...

One way: create each of the charts using the dynamic charting technique
described at
http://www.geocities.com/jonpeltier/Excel/Charts/index.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 to
skin the proverbial cat.
 
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-----
Click to expand...
 
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.
Click to expand...

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/Excel/Charts/ChartByControl.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/Excel/Zips/ChartByCheckBox2.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
 
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
 
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?
Click to expand...

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.
 
Back
Top