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





0
nospam7515 (2086)
10/20/2003 1:16:31 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
566 Views

Similar Articles

[PageSpeed] 11

On Mon, 20 Oct 2003 06:16:31 -0700, Keith R <nospam@nospam.com> 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
0
dvt_spam (111)
10/21/2003 4:44:01 PM
Keith -

This is similar to the Chart by Checkbox example on my web site:

  http://www.geocities.com/jonpeltier/Excel/Charts/ChartByControl.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/Excel/Zips/ChartByCheckBox2.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
> 
> 
> 
> 
> 

0
10/22/2003 3:19:42 PM
Reply:

Similar Artilces:

Controling queries displayed when clicking Load from Query button?
I want to be able to control the queries that are displayed when the Load from Query toolbar button is displayed. Can anyone tell me how the filter box list gets populated? Thanks. Paco ...

when displaying formulas, how to start a new line in the same cel.
In the displaying formulas mode, if the formula is too long, how to start a new line in the same cell so that the formulas can be printed in one page. (Alt + enter won't work in formula mode) Hi AFAIK the only thing you could do is copy the formula as 'Text' (e.g. put an apostrophe in front of the formula) and then use aLT+ENTER -- Regards Frank Kabel Frankfurt, Germany "EL" <EL@discussions.microsoft.com> schrieb im Newsbeitrag news:0ADE2051-6D82-439C-B65C-C9B7E0B9A925@microsoft.com... > In the displaying formulas mode, if the formula is too long, how to sta...

Pasting into Word w/o lines???
This is a multi-part message in MIME format. ------=_NextPart_000_0063_01C6C112.F0BFEDC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Does anyone have a suggestion or recommendation as to how I can cut a = piece of some data in EXCEL (without gridlines), and paste it into = MSWORD and not have the gridlines appear? Thanks, in advance Mike -------------------------------------------------------------------------= ------- I am using the free version of SPAMfighter for private users. It has removed 1162 spam emails to date. Paying ...

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

Finding the value for the point two lines cross
I have a graph of two lines that cross each other at some point. I am trying to find the value at the point the lines cross. Is there something in Excel that will help with that? On my spreadsheet i have used the INTERCEPT formula and have two numbers of where each range of Y axis values cross the X axis. There is one X axis range and twol Y axs ranges. That tells me where each Y crosses the X. How do I find where the two Y's cross. Brian wrote on Thu, 2 Mar 2006 15:46:27 -0800: B> I have a graph of two lines that cross each other at some B> point. I am trying to find the ...

Owner Draw controls....
Newbie here. Just warning you :-) Using VC 6... I've been trying to place an owner drawn combo box in a dialog, following instructions from a text. I created a derived class for my control. Since I want all the message map functions, I've placed the combo box on the dialog, and used its ID in the owner draw code. The text suggests using a "variable or pointer". I've tried adding a variable with the class wizard, and nothing happens. i.e., the control that I've placed is unaffected. Of course I attached a control variable of my derived class to the combobo...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Mail Merge Quote displays nothing but 1 line item
Hi there, I was wondering if anyone might have an idea as to why would all contents in if statements ,e.g. {IF{MERGESEQ} ="1" "}, on a mail merge does not show and anything thats not within the if statements is displayed when the mail merge is run? I eventually removed any if statement on the word mail merge and the quote format looked fine except that if the quote has multiple line items then multiple pages are diplayed. I am aware that if I use { MERGEFDIELD "LastItem" = 1} then all line items are suppose t obe displaying on one page. Any ideas would be hi...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

Tab Control Issue
Hi-- Can anyone help with why this code causes the subform inside of a tab control page to disappear? Forms!frmPurchaseOrder!pgCurrent.Visible = True Forms!frmPurchaseOrder.Section(acFooter).Visible = True Forms!frmPurchaseOrder!pgCurrent.SetFocus Forms!frmPurchaseOrder!pgAddEdit.Visible = False At first, I had 2 subforms inside of a main form. When a button was clicked, one form was hidden and the other visible. That was causing the subform to turn completely white and become useless. I decided to try tabs thinking this might solve the probl...

Anti-Phishing filter blocking many legitimate links
The anti-phishing filter in Outlook SP2 is a great idea but the implementation makes it worthless. It is blocking links in so many legitimate messages that I find I am allowing the link to be active almost automatically. Is there any information on what the criteria are for a link to be considered unsafe? How would the writer of a newsletter get his or her links off the verbotten list? there is no information on it - otherwise spammers would make sure theirs were not blocked. The only links I have blocked are ones where the hyperlink domains are mismatched. i.e., Sears uses cheetahm...

Sub-Categories for X-axis labels, line on bar chart?
I am making a vertical bar graph for pH solutions of various drug reagents. I would like to graph all my results on one chart. Preferrably, with the drug as the group label on the x-axis with multiple samples in each group. For example, samples 2, 36, and 45 I would like their resulted pH plotted in the coc group and samples 5,17, and 29 I would like their pH's plotted in the amp group. The chart should show the drug groups on the x-axis with the sample names directly on the bar itself. Just to complicate this a little further, I would also like to plot a min and max line for each dr...

Line Spacing #3
How do I adjust the spacing between lines within a cell or for an entire Workbook? Bernie Beal. You can select all the ROWS in a worksheet by clicking the place where row headers and column headers intersect (top left corner of worksheet). Then you can use Format | Row | Height to adjust the row height to a new value best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernie Beal" <bquest@optonline.net> wrote in message news:ey8e3xIDKHA.1540@TK2MSFTNGP02.phx.gbl... > How do I adjust the spacing between lines ...

Direct Debit posted control
Currently GP will assume once a customer is marked with bank account details the system will assume all invoices are taken by DD and the only time this can be amended is prior to posting to the ledger. The ability to amend a posted invoice to either be DD payment or remove the DD payment until the invoice is fully paid would be useful ...

Refreshing databound controls
If I have a combobox bound to a table on a form and additional controls on the form to add new values into the table, is there a way to refresh the combobox to show the newly added value without having to close and reopen the form first? Me!NameOfCombobox.Requery -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Dan Neely" <dan.neely@gmail.com> wrote in message news:1184610107.922449.194740@q75g2000hsh.googlegroups.com... > If I have a combobox bound to a table on a form and additional > controls on the form to add new values into t...

Zombies without Arms use Robot Remote Control
I announced a Robot Social/Health Crisis with Zombies without Arms. Instead you guys have downgraded to threating to slit each other's throats over a computer Operating System, WIndows or Linux - the problem is with homeless Robots who can't change the channel without a special Robot Remote Control. The Robots are getting desperate to a point 2 were arrested and sent back to factory for instructions on using their Remote Control, for illegally plugging into residential electric outlets to suck other Robot Dick to recharge their batteries. Our state is still imposing dea...

Combine Bar with Single Line Chart
Hello, Please can someone help me with this question. I am trying to create a bar chart with a single line chart. The bars will display the totals for various years i.e. 2004 104 2005 107 2006 108 2007 110 2008 101 The line chart will show how 2008 is performing on a weekly basis i.e. 01/01/2008 = 100 07/01/2008 = 101 14/01/2008 = 102 The bar chart for 2008 will reflect this also, updating each time a new week is added. The Y Axis I will assume display a total i.e. 99-111 for example. The X Axis needs to show the dates plotted for the line chart 01/01/2008 - 31/12/2008 (weekly) but how...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

CComboBox control
Right now I have a CComboBox that is working normally where I have the style set as a drop down list box and I load up the items in it and it shows nothing in the edit window and when I click in the edit window or the little drop down icon to its left it drops down with nothing selected and when I click on one of the items the listbox collapes and the selected item is highlighted in the edit box. What I want to have happen though is that when the dialog is displayed, the CComboBox behave as if the dropdown icon had already been clicked. I want to do this because the first thing the user must ...

Button Control Wizard Error
When I select the Form Operations in the wizard I get Invalid use of null and no actions in right pane. In the combo box wizard i get mouse up errors among others. How do I repair my wizards? Thanks If this happens on a fresh, empty db then a re-install or a repair install is in order. Tim Reid wrote: >When I select the Form Operations in the wizard I get Invalid use of null and >no actions in right pane. In the combo box wizard i get mouse up errors >among others. How do I repair my wizards? Thanks -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this...

Need a method to Dynamicly change the X axis Scale on a chart. #2
I dynamically change the amount of data on a chart. This causes the X axis Scale to either to either show too few values or too many values ( a solid black line if too many). I would like a method to dynamically change the scale values in relation to the data span. Joe - Do you want to limit the number of points displayed? http://peltiertech.com/Excel/Charts/Dynamics.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Or do you want to link the axis scales to something you calculate in the sheet? http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip - Jon ----...

Dynamically populate a drop down list
Hi, I'm trying to dynamically populate a drop down list in MS CRM (1.2) based on certain parameters. I suppose I could do it using a SQL stored procedure, but I would prefer to use standard/supported CRM customizations. Any assistance in this would be greatly appreciated. Thanks! -Dave- Dave, You can do it in 3.0 pretty easily based upon an example in the SDK... Here are some pretty pictures... http://blogs.msdn.com/midatlanticcrm/archive/2005/12/04/499868.aspx I have not tried it with 1.2, but it could probably work... Thanks Ben "Dave" <renor321@yahoo.com> wrot...

How do I put Adobe PDF file in body of email without it being in the attachment line?
I send out a newsletter in pdf but many can't get it. I recently received a pdf in the body of an Outlook message. Will this work better or is it the same as an attachment? DOn Version of Outlook? Program the non-recipients are using to receive and send email? Attachments in the body of the message (RTF - not good for non-Outlook/Eudora clients) is the same as the item in the attachment line (HTML and Plain Text.) --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without...