Input a Value to Lookup the Data Series for a Chart

[DaveM]

Hello;
I have a very simple chart (line) that I want to plot one series of data
which comes from a large range of stock symbols (6000). Col A is the symbol
in Alpha order, while col B, C, D, E, F....is the stock price at the end of
the week closing price. The first row of the range is the weekending date.
The data range looks like this...
A B C D.......
Symbol 7/17 7/10 7/3 .......
AA 5.43 6.00 6.23 ....
ABC 22.10 22.55 23.01.....

How could i input the stock's symbol on a worksheet where the chart is, have
it lookup that symbol and then plot the values in a line chart type? The y
axis on the chart is price while the x axis is the weekending dates

Thanks

DaveM

 

[OssieMac]

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.)

 

[DaveM]

Thank you OssieMac!!!

Dave

 

[DaveM]

Hi OssieMac;
works fine except only 1000 symbols in col A show on the autofilter
selection. Thoughts?

Dave

 

[OssieMac]

When you click the filter dropdown, select Custom and in the dialog box
select Equals and insert the Symbol in the field (Note for the same reason of
1000 limit you can't use the drop down to insert the symbol in the field but
you can type or copy it in).

Alternatively.
What sort of 'Symbols' are you using? Can they be divided up into groups of
up to or less 1000? If so, then you can use the following method:-

Insert an additional column.
Place a grouping character (or characters) in it to identify each group.

then

Select the group filter first.
Select the Symbol filter next.

 

[DaveM]

thank you, that did it