Optional Data Series & Missing Data in dynamic ranges?

Excel2000
Example file: http://www.mattkruse.com/temp/chart.xls

I'm not new to Excel, but two things have always bugged me and I've never
taken the time to figure out the correct answers. Can anyone help?

1) In the example file above, I have place-holders for 2 sets of data in my
chart. Is it possible to tell the chart NOT to plot a data series if it
doesn't contain any values? In other words, I'd like the chart to optionally
show 4 lines, if the data exists. But if data only exists for 1 line, don't
show the others in the legend at all. Possible?

2) In the example file, I have created defined names for a dynamic range of
values for the chart using OFFSET and COUNTA. Works fine. EXCEPT when there
are no values there at all. I get the message "Your formula contains an
invalid external reference to a worksheet" when the worksheet opens or when
anything is changed. How can I supress this error, and make the chart just
show nothing? I often work on worksheets where data is empty but will be
filled in later, and I want the charts to immediately respond and update.

Thanks!!

-- 
Matt Kruse
http://www.JavascriptToolbox.com


0
11/3/2004 8:38:39 PM
excel.charting 18370 articles. 0 followers. Follow

0 Replies
597 Views

Similar Articles

[PageSpeed] 8

Reply:

Similar Artilces:

Centralized data
I have a main costing work sheet that needs to be populated by individual costing sheets that went out to all of the buyers to populate. Main Costing Sheet A. Item # B.Desc C. QTY D. Cost E.....F.........G......H (all contain irrelevant information to costing activity. There are 5 buyers (Nick, Tanya, Doug, Skip and Forrest) that I have divided up the main costing sheet and sent them thier individual items to cost, now that I am getting the individual costs back, I need to populate my "Main Costing Sheet" with each cost, however, the items are all to...

Missing scrolls bars in web pages created with FP
http://www.bglawns.com/directions_pinewood.htm For some reason this page opens in a new window when linked to from my site, which is fine, but now there are no scroll bars either eek! Also on the into page: http://www.bglawns.com/eahorc_main.htm and the main page: http://www.bglawns.com/eahorc.htm The scroll bars are now red, should be blue. Help? Regds - Deane In line "EAHORC" <EAHORC@discussions.microsoft.com> wrote in message news:E612EBDB-301F-4F2C-896A-7C012B6D607F@microsoft.com... > http://www.bglawns.com/directions_pinewood.htm > ...

Transfer data from XML to Stream?
Hi all, I have a COM component that receives XML documents from MSMQ. Before I process the final documents and pass it to my application, I want to modify the format a bit, as in create a name/value pair structure from an element structure or rename certain fields. I'm using XmlTextReader to read over the original doc and I'm using XmlTextWriter to create a new XML file that I essentially need to load up again into an XMLDocument object. I'd rather write the modified XML to a stream local to my running instance of the COM component rather than create another physical file then ...

Toggle Data Label "Value" On and Off
Is there any way to toggle the "Value" label for a chart on and off using a button linked to some VBA code. I'd like to be able to have the label formatted (i.e. color, size, etc) so that the user just has to click a button for the values to show on the bars of the chart. Thanks for any help you can give! -Josh Found my own answer through recording macros. Here is the code I used, applying it to a toggle button. Private Sub ToggleButton1_Click() Application.ScreenUpdating = False If ToggleButton1.VALUE = True Then ActiveSheet.ChartObjects("Chart 1").Ac...

Exchange 2000 data store size does not shrink
Hi, We have an exchage 2k store that is ~16GB. We have archived ~ 5GB (PST files)and did an offline defrag but the db has shrunk by less than 2%. Currently it will not mount even with the evn when we set the temp size increase registry switch. Any ideas? In news:2B3413A7-9F3D-44E9-A0D1-06E7B1D55135@microsoft.com, KCHobson <KCHobson@discussions.microsoft.com> typed: > Hi, > > We have an exchage 2k store that is ~16GB. We have archived ~ 5GB > (PST files)and did an offline defrag but the db has shrunk by less > than 2%. Currently it will not mount even with the ...

Missing Message Text
Yesterday, Windows Live Mail started doing something strange. I type the message, then press "send." The first lines of the message somehow are removed. The recipient sees only part of the message and only part of the message shows up in my sent messages folder. The first part is just gone. Seems to be only on messages that are replies. Any ideas on what the problem is, or how to correct it, would be appreciated. Never heard of this . . . . . are you on version 14.0.8089.0726? Regardless, try a reinstallation. "DeVere" <DeVere@discussions.microsoft...

Copy a word doc to excel and hyperlink data between the 2.
I have a chart on an excel document and a long word document explaining the data in the chart. I would like to place the word document under the chart in excel. Then I would like to hyperlink the data from the chart to specific places in the word document. Help.....I have been trying to figure it out for days. Thank you. ...

Need to extract data mid-string.
Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754And here's what I'm trying to get back: Swissco\Default.htmThe number of characters from the left will always be the same, 28. And thestring will always be trimmed after the ":" (colon). I'm struggling with how to use mid, left, right to get out what I need. Thanks in advance!Kellie-- Message posted via http://www.accessmonster.com On Mon, 26 Mar 2007 21:19:27 GMT, KFox via AccessMonster.com wrote:> Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754> And here&...

Re: Data Summary
Can we do the summary by defining a macro? The difficulty with the suggested solution : 1.I am not able to advance filter the unique data to a separate sheet. 2.Every day the data gets changed, so have to repeat the exercise daily and is very time consuming. Regards, Jimmy Joseph Subject: Re: Data Summary From: "Ardus Petus" <ardus.petus@laposte.net> Newsgroups: microsoft.public.excel In summary sheet, you can get the totals with a formula like: =SUMIF(Products!$C$2:$C$7,A2,Products!$A$2:$A$7) See example: http://cjoint.com/?fmkJhzVi8q To automate the production of ...

Add more than one series to a pivot chart using VB MS Access continued...
I'm trying to programmatically create a stacked bar pivot chart. Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've created the chart. However, it doesn't distinguish between the different values for the series. Does anyone have any suggestions on how to create a chart using one column containing three values for the series? Essentially, this is a continuation of a previous post: http://groups.google.com/group/microsoft.public.access.formscoding/browse_thread/thread/e23f3506a6d561a0/674295d410a71cf9%23674295d410a71cf9" Any help is greatly appre...

Randowm data corruption Money 2002
Help! I use Money 2002 Deluxe & Business to manage 6 business bank accounts and 2 petty cash accounts. All of a sudden, Money has started to randomly change some of the entries in the petty cash accounts. It changes them in increments of $50, $100, $150 or $200. For example, one entry that was originally $200 on Jan 1, now reads $400. One on 2/2/04 for $70.40 now reads $170.40. Conversely, on 4/17, the amount was $800 and it is $600! There are some 20 such changes that I have detected so far. I am the only one using this program. I can't see any pattern to the changes (payee, ...

Pulling Data from one worksheet into another
Hi, I have a question regarding pulling text from one worksheet into another. Please can you help me? I need to duplicate the information (text only) on one worksheet to another one as it is typed. Is there a formula I can use that will allow the data typed in one worksheet to appear in the other one immediatley? I have a formula that the person before was using but I don't know how to apply it to the whole sheet. Any advice would be greatly appreciated. The formula is =IF(ISBLANK(OFFSET(OFFSET('Design Schedule'!$B$8,$A15,$B15),0,H$7)),"",OFFSET(OFFSET(&...

Dynamic Chart :S please help me !!!
Damn , I'm getting so confused with this so please help!! This is what I want to do. I've got one databasetab with this column : week1 week2 week3 week4 object1 3 4 5 45 object2 43 34 43 43 object3 88 554 object4 and so on. Now ik want on another Sheet a box where I can fill in the wee number and than a chart wil show only the numbers of that week as: WEEK # 1000 500 0 -----------------------------------------------------------------...

Excel 97 on Windows XP data sort error
I have had sorting errors using Excel 97 running on Windows XP. Is there a fix for this? I check Excel updates and the microsoft website says I already have all updates. Please tell what sort of errors. "Steve P" <anonymous@discussions.microsoft.com> wrote in message news:00af01c39601$ad0c3d20$a001280a@phx.gbl... > I have had sorting errors using Excel 97 running on > Windows XP. Is there a fix for this? I check Excel > updates and the microsoft website says I already have all > updates. Sort errors are almost always data problems. You might have numeri...

Collect data for a SQL Server DB via Infopath
Can anyone direct me toward information that discusses using InfoPath forms as a means to capture data for a SQLServer database? http://office.microsoft.com/en-us/infopath/HP101070601033.aspx -- Susan Ramlet **please reply to the newsgroup so others may benefit** "David C. Holley" <David.C.Holley> wrote in message news:eqFN06IuKHA.652@TK2MSFTNGP05.phx.gbl... > Can anyone direct me toward information that discusses using InfoPath > forms > as a means to capture data for a SQLServer database? > ...

how to reverse a series batch posting in the GL only
we have a batch from invoice batches. The GL numbers are wrong.. we want to reverse the GL journal entries only. how can we do this. We will put in a new Journal entry for the correct numbers. don't want any of the details in the invoices touched... thanks -- Linda W. Hi Linda, The backout feature in GL will work only on transactions originated in GL. You can always create a summary correcting entries in GL for the entire invoice batch instead of creating JE per invoice document. Hope this helps! Cheers, Dennis Araullo, MACS Microsoft Certified Techn...

How do i get excel to add a range of times & display as total as .
Hi I'm trying to get excel to add a range of times together and display the total as the sum of all of these hours. I can enter the times but keep getting the strangest of totals. Any help please. Use a custom format [hh]:mm for example otherwise it will always start over after 24 hours so 30:00 shows as 06:00, later if you need to apply a pay rate note that you have to convert to decimals by multiplying with 24, i.e. =time*24*payrate Regards, Peo Sjoblom "pm_faery" wrote: > Hi > I'm trying to get excel to add a range of times together and display the > ...

looking for control to display dialog similar to VC NET option window
I would like to implement a dialog similar to the VC++ NET options window. The left side of the options windows contains a tree control used to select general category of options that will be viewed/edited. The right side of the options window displays an dialog that allows the user to view/edit specific options. Are there any shareware/commercials controls available that provide this functionality? Thanks in advance. Ian I think I've seen something like that in www.codeproject.com (and for free) ...

Publisher mailmerge error message, invalid office data source
I am trying to edit a mailmerge database I started in publisher, but I keep getting an error message, "This is not a valid office data source". But it was just fine earlier. Are you creating the list in Publisher? What version Publisher? Look in "My Documents", "My Data Sources", is your data base there? Is there a number associated with your error? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Obrother" <Obrother@discussions.microsoft.com> wrote in message news:31B77242-6579-4B64-9...

How do I eliminate duplicate values in a series of data in excel?
see http://cpearson.com/excel/duplicat.htm In article <67F30DC1-DA2D-4144-8C9A-99793AD92302@microsoft.com>, "dparker" <dparker@discussions.microsoft.com> wrote: ...

Screen or print option while printing report
Allow a default for the company or the user that can set the report option ( Screen or Print ) -- Regards Santosh ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=8c0c7c3b...

Data type mismatch
New to Access 2007 I'm putting together a database. One of the fields is test scores which have a value of 0 through 100 OR "ND". They aren't willing to get rid of the "ND", so I have the field set to "Text". Is there a way to create a validation rule that limits input to these values? A combo box with 100+ values seems awkward. I tried: Between 0 and 100 OR "ND" but I got a type mismatch error. Thanks This worked in Access 2003 as the validation rule for the control: ="ND" Or (>=0 And <=100) For...

Excel can't SAVE added data & formatting?
Excel 2000 ... I have a single page spread sheet (my own) that was working fine. After expanding the spread sheet to handle more equipment I received error message stating: Excel can not SAVE all of the added data & formatting ... Here I had the option to select OK without fixing ... CANCEL ... or ... HELP. I selected HELP & ended up on a blank HELP Page ... So now I am turning to this board. What gives? Could I be running into a formatting limit issue? Sheet less than 1000 rows, but many formats ... Thanks ... Kha "Ken" <anonymous@discussions.microsoft.com...

Apply a mutiple to a range of cells?
I have a range of cells spanning multiple columns and rows. I would like, in my fantasy, to just hightlight all of them and say "multiply by x" where x is an integer. Now, I know I can't do that because my microphone doesn't work, so, other then a macro, is there a way to do it? Thanks, -Jim In an unused cell enter the numnber that you want to multiply by - it can be an integer or a floating point number - then highlight the range to you want to mutiply and then right-click and select Paste Special > Multiply > Ok Don't forget to say "Multipy by x"...

Data Download
Hi Team, I was wondering if someone there could assist me with a small problem. I'm downloading data into Excel, which is to large for 1 standard spreadsheet. Is there a program, macro or add-on that you could suggest that will split the information automatically into multiple spreadsheets? Thanks for your help, Chris -- Chriso ------------------------------------------------------------------------ Chriso's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34285 View this thread: http://www.excelforum.com/showthread.php?threadid=540515 Depends on how...