Generate Chart Based on Cell Values

[Guest]

Hi,
I've written a macro to import data points from nearly a hundred workbooks
based on inserting cell values into a filename reference. Now, I'd like to
take the data points and generate a chart based on 2 criteria (possibly more
in the future). The first criteria can be chosen from a list in cell A2. This
criteria is a reference to a specific worksheet. The second criteria, which
can be chosen from a list in A4, refers to a specific column in a worksheet.
So, in essence, i'd like to take two cells, one referring to a worksheet, and
another referring to a column and enter the data into a chart in a seperate
worksheet. Ideally, entering in new values into the reference cells will
update the chart, and not generate a new one. Any suggestions would be
appreciated.

 

[Jon Peltier]

I have made a sample that does this. In a master sheet in the workbook, I
have two listboxes, one to select a worksheet (Sheet1 through Sheet10)
within the workbook and one to select a column (Series A through Series H)
in the chosen sheet. A chart displays data from columns A and B of the
master sheet. Based on the listbox selections, INDIRECT functions in the
chart source range look up the data in the selected column of the selected
sheet. When the listbox selections are changed, the chart automatically
responds.

The marginally documented workbook is posted on my web site:
http://peltiertech.com/Excel/Zips/ChartByListBox.zip

Similar examples are described on this page:
http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon