Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Input a Value to Lookup the Data Series for a Chart
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Input a Value to Lookup the Data Series for a Chart
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Input a Value to Lookup the Data Series for a Chart
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Input a Value to Lookup the Data Series for a Chart
Hi OssieMac;
works fine except only 1000 symbols in col A show on the autofilter selection. Thoughts? 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Input a Value to Lookup the Data Series for a Chart
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. -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Input a Value to Lookup the Data Series for a Chart
thank you, that did it
"OssieMac" wrote: 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. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cant input series data | Charts and Charting in Excel | |||
chart with two data series and two colors for each data series | Charts and Charting in Excel | |||
Lookup function with condition in time series data | Excel Worksheet Functions | |||
automatically expand chart data series as data is added | Charts and Charting in Excel | |||
chart data series -- plot a table as a single series | Charts and Charting in Excel |