Chart Window

[zephyr]

I have a spreadsheet with six rows 2 to 7 and 12 columns B to M.

Row 2 contains the date. Rows 3 to 7 contains the data.

The data from the cells is used to display a chart on the worksheet.

What I would like to do is

1) have the chart to only appear on a window when a button is pressed
2) when the window appears there will be two boxes start date and end date.

When the start and end dates are typed in I would like the chart to only
show the points between the two dates. If the start date is not typed in then
the full range should be displayed automatically.

Could any one advise how this can be made to work, a step by step approach
would be appreciated as I am not a whizz with excel.

Many thanks and any help is appreciated.

 

[Jon Peltier]

If you adjust your configuration slightly, you could use this approach to
select which of several charts appears:

http://peltiertech.com/Excel/Charts/ChartTypeDropDown.html (use the Bring To
Front technique)

or this combo box approach to select a series to show in a single chart:

http://peltiertech.com/Excel/Charts/ChartByControl.html

Then use this for the date range:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

- Jon

 

[zephyr]

Many thanks for your response. I was trying to hide the charts but I have
looked at the example and it is a good solution to my problem.

I have been trying to get the combo box to work on my application. The
example provided has one line on each graph. The graph that I have has six
lines.

The problem that I have is that I cannot get the table source data to link
to the combo box. On the example given the table source data reads:

=’Chart Combo Box’!$A5$A9,’Chart Combo Box’!$E5$E9

In my application the table source data reads:

=’01 Sales’!$I$159: $U$159, ’01 Sales’!$I$166: $U$176

01 Sales is the name of the worksheet, when I put in ’Chart Combo Box’ an
error message pops up. I have inserted the ’Chart Combo Box’ and tried
removing ’01 Sales’ but the error message keeps on popping up. Is there a way
to link the chart to the combo box?

Many thanks

 

[Jon Peltier]

The chart does not link to the combo box. The chart always plots the data in
the tan shaded range:

='Chart By Combo Box'!$A$5:$A$9,'Chart By Combo Box'!$E$5:$E$9

'Chart By Combo Box is the sheet name, A5:A9 is the column of X values, and
E5:E9 is the column of Y values. You need to make this consistent with the
sheet name and cell addresses of your data.

The combobox' list source (the list of displayed entries) is in the green
range (H2:H4), while it is linked to the yellow cell (H1), which contains
the index of the selected item in the list. Formulas in E5:E9 make sure that
values from the appropriate column (B through D) are displayed in column E,
according to the index in cell H1.

- Jon