If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamically control how many lines on a graph?
I apologize if this is a FAQ, for some reason my newsreader isn't picking
up any messages from this group (looks like no postings) so hopefully I'll be able to access responses, if not, I'll googlesearch for responses later today and tomorrow... ------------------------------------------------ I have many sets of data, all on the same sheet (in a repeated pattern- starting in row 1, then 11, then 21, etc) My single graph is based on named ranges, and the named range(s) are based on Steve Bullen's funchart1 (autoexpanding chart) to control how many x-axis values are shown- which is great, because each set of data may have a different number of data points. I control which data set is used on the graph through a reference cell (A1) which is part of the named ranges, so it changes what data is shown on the graph automatically. The problem is that I have "up to" 5 series on each graph, and I need to show the legend. I know how many series I need on each graph (and can put that in a cell next to the data), but I need to only "show" the correct number of series. e.g. if there are 5 series, I want to show them all, and if there is only one, I want to show only that one on the graph. In my dreams, there would be a way to link to that cell that shows how many series should show on the graph, and have it "suppress" any of the additional (blank) series that are referencing columns of blank data. It matters because those extra series affect the column chart's column width and placement, and it the extra labels still show up on the legend, which makes the legend bigger, and implies that there is other data there, even when there isn't. Does anyone have an easier way to (as automatically as possible) include/exclude series based on whether there is data to populate those lines? Best case, a way that preserves formatting when those lines are reinstated, so I don't have to reformat the reinstated lines each time they come back? I can't think of a way to do this without VBA, so I'm wondering if maybe there are some cool chart control options that I've never had to learn about before that might be helpful. If it does require VBA and someone else already has a code snippet they'd be willing to share, I'd appreciate that as well, just to save a little time on this project so my boss can have the graphs sooner rather than waiting for me to figure out all the details. :-) Many thanks, Keith |
#2
|
|||
|
|||
Dynamically control how many lines on a graph?
On Mon, 20 Oct 2003 06:16:31 -0700, Keith R wrote:
The problem is that I have "up to" 5 series on each graph, and I need = to show the legend. I know how many series I need on each graph (and can = put that in a cell next to the data), but I need to only "show" the correc= t number of series. e.g. if there are 5 series, I want to show them all,= = and if there is only one, I want to show only that one on the graph. What kind of chart? I ask because your post refers to lines and columns= .. If it were a line or xy scatter chart, I could imagine plotting all 5 = series. The data for the nonexistent series might be blank cells or = contain N/A errors. The default Excel legend would be replaced by your = custom legend, which would be created using dummy series. A line chart = would probably need to be converted to an XY scatter chart; more work = would be required to work out all the details. -- = Dave dvt at psu dot edu |
#3
|
|||
|
|||
Dynamically control how many lines on a graph?
Keith -
This is similar to the Chart by Checkbox example on my web site: http://www.geocities.com/jonpeltier/...ByControl.html In brief, there is a checkbox for each series that might be charted. When each checkbox is checked, it runs a macro that redefines the source data range. I put an undocumented workbook on my web site: http://www.geocities.com/jonpeltier/...yCheckBox2.zip The checkboxes are linked to cells in the sheet, which are named "boolY1", "boolY2", and "boolY3". The data for the chart is in ranges named "theX", "theY1", "theY2", and "theY3". The macro checks each of the boolYi values, and if it's True, it uses Union to combine that theYi range with theX and the other theYj ranges with True boolYj. Then it sets the source data of the chart to this new combined range. Someday I'll write a new page for this example. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Keith R wrote: I apologize if this is a FAQ, for some reason my newsreader isn't picking up any messages from this group (looks like no postings) so hopefully I'll be able to access responses, if not, I'll googlesearch for responses later today and tomorrow... ------------------------------------------------ I have many sets of data, all on the same sheet (in a repeated pattern- starting in row 1, then 11, then 21, etc) My single graph is based on named ranges, and the named range(s) are based on Steve Bullen's funchart1 (autoexpanding chart) to control how many x-axis values are shown- which is great, because each set of data may have a different number of data points. I control which data set is used on the graph through a reference cell (A1) which is part of the named ranges, so it changes what data is shown on the graph automatically. The problem is that I have "up to" 5 series on each graph, and I need to show the legend. I know how many series I need on each graph (and can put that in a cell next to the data), but I need to only "show" the correct number of series. e.g. if there are 5 series, I want to show them all, and if there is only one, I want to show only that one on the graph. In my dreams, there would be a way to link to that cell that shows how many series should show on the graph, and have it "suppress" any of the additional (blank) series that are referencing columns of blank data. It matters because those extra series affect the column chart's column width and placement, and it the extra labels still show up on the legend, which makes the legend bigger, and implies that there is other data there, even when there isn't. Does anyone have an easier way to (as automatically as possible) include/exclude series based on whether there is data to populate those lines? Best case, a way that preserves formatting when those lines are reinstated, so I don't have to reformat the reinstated lines each time they come back? I can't think of a way to do this without VBA, so I'm wondering if maybe there are some cool chart control options that I've never had to learn about before that might be helpful. If it does require VBA and someone else already has a code snippet they'd be willing to share, I'd appreciate that as well, just to save a little time on this project so my boss can have the graphs sooner rather than waiting for me to figure out all the details. :-) Many thanks, Keith |
Thread Tools | |
Display Modes | |
|
|