Thank you OssieMac!!!

Dave

"OssieMac" wrote:

Hi Dave,

You can do this with AutoFilter and a simple macro. The example assumes that
your first column header is in cell A1 and your symbols are in column A.

Apply AutoFilter to you data. (See Help for how to do this).

At the bottom of the data leave a few blank lines and insert the following
formula in the cell in column B.
Note in the formula B2 is the first row of data under the column headers and
you need to edit B10 to match the last row of your data.
=SUBTOTAL(9,B2:B10)

Copy the formula across to the remaining columns.

(See Help if you want more info on what SUBTOTAL function does.)

Right click the worksheet tab name and select View Code to open the VBA
editor.
Copy and Paste the following code from (Private Sub to End Sub) into the VBA
editor.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If AutoFilterMode Then
If FilterMode Then
'Edit A12 to match the row where you
'inserted the formulas.(Say A6005)
Range("A12") = _
Mid(ActiveSheet _
.AutoFilter.Filters _
.Item(1).Criteria1, 2, 255)
End If
End If
Application.EnableEvents = True
End Sub


Edit the cell range A12 in the code to match the row where you inserted the
above formulas.
Click the X (top right of window) with red background to close the VBA editor.

Click the AutoFilter dropdown in column A and select one of your symbols.
Only the row matching that symbol should be displayed and the symbol will be
copied by the macro to the row with the formulas.

Create your chart by selecting the column headers and then hold the Ctrl key
while you select the data where the formulas are. (Note select from column A
for both headers and columns)

Now each time you change the AutoFilter the chart will change to match the
displayed data.

If using xl2007 then save as an Excel enabled workbook. For previous
versions you will need to set security in the Options to medium and approve
the use of macro each time the workbook is opened.

A tip when using non contiguous dates for the axis in charts. The dates need
to be in text format when you are using week ending dates otherwise the chart
will incorporate the intermediate dates. (To insert the date in text format
precede the date with a single quote when entering.)


--
Regards,

OssieMac