Automatic Dynamic Chart Ranges

Hi:

I have an XY plot that refers to a set range of values on my worksheet
In this case, it is 8 rows long.  I chose 8 rows because that is th
maximum number of data points that I will ever need.  I have set up th
chart values so that they are dynamically calculated based on VLOOKUP
from other data.  Sometimes there are only 2 data points, sometimes 
or 6.  The problem is that no matter how I set up the formulas, Exce
does not like the cells without values in them being included in th
chart series (I have hardwired it to include all 8 rows).  I thought i
might ignore a blank ("" in the formula), but it does not, and adjust
the whole series to a "line" series (integral values of 1, 2, 
regardless of my X data).  First of all, is there any way around thi
issue directly, i.e. can I put a result in the cells which don't hav
data that Excel will ignore?  Of course I could erase the cell
manually or set up a macro, but I am doing this on hundreds o
different worksheets.

I have also considered dynamic ranges, but I can't find any solutio
which does not involve creating named ranges, which will be ver
difficult with all the different worksheets I must create.  Apparentl
Excel does not allow much in the "SERIES" keyword for its charts - 
have tried INDIRECT, OFFSET, etc., without any success.

I have checked this forum (albeit somewhat superficially, as I am no
quite sure what to search for) and have not found any threads relatin
to this - if there are some, please point them out to me.

Thanks for any assistance,

John 

--
Message posted from http://www.ExcelForum.com

0
7/27/2004 1:42:52 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
353 Views

Similar Articles

[PageSpeed] 48

Two possible items of use to you:
i) Example of dynamic chart at www.stfx.ca/people/bliengme/ExcelTips
ii) Rather that using spaces (as in " ") use N/A (as in NA()) and Excel will
ignore when charting

best wishes

-- 
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"jwa76 >" <<jwa76.1a21te@excelforum-nospam.com> wrote in message
news:jwa76.1a21te@excelforum-nospam.com...
> Hi:
>
> I have an XY plot that refers to a set range of values on my worksheet.
> In this case, it is 8 rows long.  I chose 8 rows because that is the
> maximum number of data points that I will ever need.  I have set up the
> chart values so that they are dynamically calculated based on VLOOKUPs
> from other data.  Sometimes there are only 2 data points, sometimes 5
> or 6.  The problem is that no matter how I set up the formulas, Excel
> does not like the cells without values in them being included in the
> chart series (I have hardwired it to include all 8 rows).  I thought it
> might ignore a blank ("" in the formula), but it does not, and adjusts
> the whole series to a "line" series (integral values of 1, 2, 3
> regardless of my X data).  First of all, is there any way around this
> issue directly, i.e. can I put a result in the cells which don't have
> data that Excel will ignore?  Of course I could erase the cells
> manually or set up a macro, but I am doing this on hundreds of
> different worksheets.
>
> I have also considered dynamic ranges, but I can't find any solution
> which does not involve creating named ranges, which will be very
> difficult with all the different worksheets I must create.  Apparently
> Excel does not allow much in the "SERIES" keyword for its charts - I
> have tried INDIRECT, OFFSET, etc., without any success.
>
> I have checked this forum (albeit somewhat superficially, as I am not
> quite sure what to search for) and have not found any threads relating
> to this - if there are some, please point them out to me.
>
> Thanks for any assistance,
>
> John A
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bliengme5824 (3040)
7/27/2004 2:09:31 PM
Ah, I see.  This is a very good tip.  I used the NA() for some of m
data, but there is one set of data that I use for several charts, and 
want all the data to appear on some charts, but not on others.  

Basically, I have it set up like this:

=IF(TODAY()>('Fiscal Calendar'!B11+9),R274/R$299,NA())

where Fiscal Calendar'!B11+9 is the date whereafter I want the data t
appear on the chart, and this is the formula for the *actual
man/hours.  In my chart, I compare this data to the man/hour
available.  In this case, I don't want available data to appear afte
the current fiscal month.  Thus, the NA() formula would work.

However, when I compare the man/hours available to the man/hour
scheduled, I want data to appear until April 06.  I am using the sam
source data for available man/hours for both charts.  Is there any wa
to get the available man/hours data to appear on th
available-scheduled chart, but not on the available-actual chart?

Thanks a billion!

Stephani

--
Message posted from http://www.ExcelForum.com

0
7/29/2004 4:11:43 PM
Reply:

Similar Artilces:

stacked bar chart and a bit more...Excel 2003
Hi folks i need one graph to show 2 things: the x axis will show months (april to march). the y axis will show a % error rate (ie. from 0 - 10%). so, for example, if i wanted to show 8% error rate for march I'd have a column approx.80% of the height of the graph. Howewver (and this is where i need the help), I want a stacked bar chart so the column displays the numeric values of those errors (e.g. suppose the march errors were: 3x type a, 4x type b and 1x type c error). In a nutshell the chart is supposed to show both the failure rate and breakdown of errors over time.... I don&...

Can the number of series be dynamically linked to contents of a cell?
It seems like this would be a simple thing to do, but I can't figure out how: I want my chart to use one column of data from a table if a certain cell has the value 1. If the cell says 2, I want the chart to use two columns of data from the table. If the cell changes back to 1, I want the chart to revert to the first series only. Thanks in advance for any ideas on how to do this. Bruce I should clarify that when the cell value is 1, the bar chart should display a single series with bars spaced as if never was any second series. In other words, blanking the data for Series 2 won...

How do you split the Y-axis on an Excel chart?
I've seen this done where the Y-axis is "split" to accommodate a big leap to a high number (outlier). Take a look at Jon Peltier's Broken Y axis documentation and see if it helps. http://www.peltiertech.com/Excel/Charts/BrokenYAxis.html "RickiRebel" <RickiRebel@discussions.microsoft.com> wrote in message news:5E33B899-820C-4042-B186-031A18452AE4@microsoft.com... > I've seen this done where the Y-axis is "split" to accommodate a big leap to > a high number (outlier). ...

Document regarding Isntallation of Frx Report Server for Dynamics GP 10
I am looking for documentation regarding installation & setup of Frx Report server on GP 10.0 We would like to be able to design and schedule Frx reports and forward resulting reports to an email address (that will put the reports in a Sharepoint document library) Your help is greatly appreciated. Bill ...

Access 2002 bug: "dynamic" forms problem
There was no respond to my original post in microsoft.public.access.forms (May 5th 2007) about a fortnight so I try it here... ��������������������������������� A while ago we had a discussion about A2002 (sub)form which changes dynamically from code (hide/unhide controls, change subfom's width, etc.). There's a problem if I make some changes in code when the form is open: if I save changes, A2002 saves controls positions & subform's size as well. This is a BIG A2002 BUG and it's really annoying. Why Access forms don't behave as Visual Basic forms? I don't want Acc...

Deleting Range name's listed in the range address box.
In Excel, in the top corner of the application there is a box which list the name of a select range (e.g F19 etc..). when I use the querytables method to import a CSV onto a worksheet. This range address box, has the name of the data I have imported. when I use clear, or qyertytables delete to remove the imported data - I am unable to remove the name of the data appearing in the drop-down list for the range box. Is there anyway to delete this ? or is it not a problem ? I am concerned because I this rangelist seems to fill up pretty quickly with imported data ranges.. Choose Insert&...

Fill scroll window with range from procedure?
Hi All, I have been trying to fill a scroll window from a form procedure. The problem I am running into is that when I call the 'fill' statement, the executing stored procedure passes two null variables instead of the values I set for the range. I know the code works as I can put it on the Form's Pre event and the scroll window is populated with the correct restrictions, but I moved the code to a form level procedure so that many forms could call the procedure and have the scroll window filled. Here's the code: range clear table POP_WisdmActuals; 'PO Number' of ta...

access 2007 dynamic creating child and master link fields on subfo
I have a form used for searching, and a subform displaying by default a datasheet of the table being searched. there is a listbox above that displays the results as the user types. with a click of a button, those results can be mirrored to the subform (recordsource is changed through vba). this works fine i naccess 2003. in access 2007, after the update, access seems to be automatically creating master and child link fields on the subform between itself and the main form. it is doing this on it's own on 5 forms set up the same way but for searching different tables. any...

Bubble Chart best fit Scatter plot
I need to create a scatter plot that will have an oval shape bubble that only circles the top 75th percentile date ponts. I currently have a scatter plot with data points and then I use an excel object (oval) and size it manually over the chart to fit the top 75th percentile data points. Is there a functions or VBA code I can use to create this best fit oval automaticaly or some type of trick I can use in excel? Thank you Hi Tedasu, Have a look at Fernando Cinquegrani's site http://www.prodomosua.eu/ppage02.html Ed Ferrero www.edferrero.com I don't see what I'm looking ...

Control Charts #4
I have not created a control chart before, so please excuse my ignorance. A few questions: 1. If a quality control chart is a part of a report (in MS Word), should it be imported or copied into a MS word document (there is text also in this report) or can you type the text in excel so that you don't have to import or copy the control chart in MS Word 2. How is it best to start a control chart? 3. How do you add text inside the chart? How do you change the line appearance 4. Are line formats best to use for a control chart? Again, excuse my ignorance, I appreciate any info...

Chart title = Worksheet Name?
I have a workbook where every sheet represents a different day. The title of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose title is same date as on the tab, but formatted slightly differently: Thursday <cr> September 14, 2006. Is there a way to easily link the two so when I update the tab date, the chart date changes also? Thanks, Bill Halper I do hope someone can find a shorter way but here is mine. Firstly, the name on the tab will not be recognized as text by Excel, so wee need to get the sheet name into a cell and then extract year, month day A1 (sheet na...

Automatic mail retrieval with dial up
I am using Windows 2000 with dial up and ATTGlobal's dialing software. Is there a way to setup MS Outlook to automatically connect to my ISP, say once a day at 2:00 AM to get my mail? If so, how? Paul ...

blood pressure chart for excel?
Is there an excel app for graphing one's blood pressure over a hours in a day or across months, years? -- Wayne Watson (Watson Adventures, Prop., Nevada City, CA) (121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time) Obz Site: 39� 15' 7" N, 121� 2' 32" W, 2700 feet Solid waste produced to generate electricity per person over a lifetime: Nuclear power -- 2 pounds in a coke can Fossil (coal) fuel -- 68.5 tons in six 12-ton RR cars -- Power to...

Export range to text file
I need to export a range from excel to a text file with a delimiter such as a comma or semicolon. I want to be able to have some kind of macro that will check each row and if a row is blank then it will stop there and only export the rows that has data. Which is why I was thinking of using a range, or array. I do have some formulas in the cells so do not want that to show up in the export. Thanks, -- Matt Scheperle mscheperle@gmail.com I have this code that I use to create a csv for later update some values in a system I hope it works Dim ColId As Integer Di...

multiple ranges & criteria for SUM-IF challenge
Not sure if this is the function for the job but I need to have SUMIF compare values in multiple ranges/columns and add if the values match in those columns e.g. if the value in range A5:A10 = value in P5 and value in range B5:B10 = value in Q5 and value in range C5:C10 = value in R4 and value in range D5:D10 = value in S5 and value in range E5:E10 = value in T5 sum range being Z5:Z10 I hope this makes sense. Can clarify further on request. Thanks Hi Try =SUMPRODUCT((A5:A10=P5)*(B5:B10=Q5)*(C5:C10=R4)* (D5:D10=S5)*(E5:E10=T5)*Z5:Z10) -- Regards Roger Govier "akumudzi" <...

Macro error, range object need data?
Hi all How can I improve my macro which has a macro error like below: "Macro error The method can't be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails." How can I modify range object which has some empty data in the range? Thanks Daniel Hi, Does the range you are referencing contain data? Empty cells or those containing #N/A can cause problems when using the charting section of the object model. Cheers Andy Daniel wrote: > Hi all > How can I improve ...

How to get Outlook to open automatically when computer loads up?
How can I get Outlook to open up automatically when I turn on my computer? On Sun, 6 Sep 2009 12:41:01 -0700, hgonzale <hgonzale@discussions.microsoft.com> wrote: > How can I get Outlook to open up automatically when I turn on my computer? This is not really an Outlook question. You get any program or programs to start automatically when you boot the computer and you can do each of them the same way. The easiest way to do this is simply to put a shortcut to each program you want to start automatically in the Startup folder: C:\Documents and Settings\your user name\Start Menu\Pro...

Excel Charts 02-24-10
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello -- <br><br>Recently I've been unable to get my charts to display after clicking on Gallery and Charts. The gallery is supposed to drop down and it no longer does. The button will highlight in green - but will not work. What is going on? ...

creating a combobox dynamically on an excel sheet
hi, i'm totally new to excel and writing macros and code for it. i need to create a file which will allow the users to click on the 11th column on any row and select a value from the combobox that appears in that cell on clicking. the combobox will only appear if it is the 11th column. i plan to create one combobox and make it invisible when the file is loaded. it will become visible in row the user clicks (only if the col = 11). i want to use VBA for this. but i have no clue how to! any help will be greatly appreciated! thanks!! gupt -- gupt --------------------------------------...

dynamic dependent list
I have used the method described on Debra Dalgleish's web site to create data validation in column B that is dependent on what is entered in column A. So, column A is validated by a list, the source of which is =list1 The name "list1" is defined by the equation =Sheet2!$A$2:$A$21 These cells contains "item1", "item2" and so on. Column B is validated by a list, the source of which is =INDIRECT(A1) The name "item1" is defined by the equation =Sheet2!$B$2:$B$21 and the name "item2" is defined by the equation =Sheet2!$C$2:$C$21 and so on. All o...

pivot chart changes not saved
I have a report that incorporates a pivot chart. It's been acting squirrelly lately in that modifications I make to the chart (form in pivot chart view) aren't saved. For example, I noticed a Title under the Title that I defined that reads "multi-chart." I've tried to delete this field to no avail--when I open the report, it still has a title "Multi-chart." I don't try to modify the chart with the report open, I've deleted the field using the del key as well as the delete "x," and I hit the save button before closing out. Any ideas why the...

Office Chart 10.0 on Windows Forms(.NET)
Hello Developers, I would like to use a line-chart component onto my Windows Forms to show the changes in Values (Y-axis) versus to Time (X-axis). I need a continuously changing data flow 4-6 times per second. Is this possible with Office Chart 10.0 component at Line-type chart on the Windows Forms? I thank your consideration in advance. -- İyi Çalışmalar Alper AKÇAYÖZ (Bil Muh) Wish You Good Work Alper AKCAYOZ (Bil Muh) ...

Match and Sort for two range of data on different worksheets?
Hi all, I had a tedious problem here. I had two worksheets on e same workbook whereby I have one range of data on Sheet 1 and e other range of data on Sheet 2. [Data given to me]... The data on sheet 1 had info on 1st half of financial year whereas e data on sheet 2 had info on 2nd half of financial year. It appears as below: Sheet 1 (1st half) Sheet2 (2nd half) --------- -------- Company Name Sales Fig. Company Name Sales Fig. BAA Limited ...

Starting GP Automatically in Terminal Server 2008
Does anyone have any thoughts on how I can start GP automatically when our users log onto our Terminal Server? Currently, we have terminal services running in WS2003 and it works fine by setting the environment in active directory. For some reason, this doesn't work in server 2008. I think it has something to do with launching the .set file in conjunction with the Dynamics.exe file. The remote app service is not an option in this case as my thin clients do not have any type of OS, simply a connection manager (HP Thin Connect). Can anyone offer any suggestions? Thanks! -- Aaro...

Gantt chart questions
I have an old version (2000 standard) and have created a Gantt chart with it. The chart is small (about 43 lines). After I saved it, reopening the file takes a lot of CPU (about 20 minutes on a P4 2.2 GHz) and large working set (about 900MB). Is this a known issue? Is this problem fixed in a later version or Service Pack? Bill Honaker XID, LTD Remove 'NOSPAM_' to reply via email if you must ...