I have designed a couple of graphs using named ranges to give dynamics to the number of series in a chart. This works fine as long as I don't want to copy and/or move the sheet to a new work book. When I do this, Excel chooses to remove all references to the named ranges in the SERIES forumla and replace them with fixed references (despite having scoped all named ranges to WS scope).
From googling I found the tip to save the WB as a template and use Insert sheet to add the graphs. This does not quite work as expected though. The SERIES formula points to the right named ranges, but when I update data the graph is not updated. If I save the new workbook and reopen it, it works as expected. Other quirks include not being able to Insert a second template without saving and reopening (the named ranges points to wrong sheet if I do not do this).
So my question is two-fold; has anyone seen this problem before and solved it? Or, has anyone been able to successfully copy/move a sheet and keep the named ranges without involving a "relink" macro?
Hi,
I am after some help on how to plot a normal distribution curve for some data entries using excel. The numbers attached are some of the counts of cars over an hour period, from 8:00am til 9:00am, on numerous Wednesdays over the period of a year over a stretch of road. I do not know how to do this as I have not been exposed to normal distribution in excel. Would you be able to please show me how you did it and the steps you took to achieve this.
My numbers for the counts of cars a 2329, 2207, 2731, 2720, 2595, 2825, 2810, 2851, 2806, 2564, 2802, 2710, 2679, 2825, 2852, 2915, 2968, 2808, 3023, 2907, 1939, 12842, 2855, 2828, 2715, 2775, 2925, 2875, 2753, 2880, 2828, 3042, 2941, 2898, 2873, 2963, 2909, 2746, 1654
Sorry, I would have put them in a excel doc but am unable to..
Thanks for the help! Really appreciate it.
Travis
I'm trying to paste an image as a background to a radar chart. The objective is to let the user choose from a number of background images to suit what is being shown on the radar chart.
I've tried recording a macro but it skips the vital step of pasting the image from the clipboard. (This isn't how I'll actually use the code but I've left the macro as recorded for completeness).
Code:
ActiveSheet.Shapes.Range(Array("Picture 21")).Select
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.ChartObjects("Radar").Activate
ActiveChart.PlotArea.Select
'this is where the formatting happens but the macro hasn't recorded it
Can anybody help fill the gap?
An alternative would be to have a stack of images behind a transparent chart that get made visible as required. Code for making sure they line up exactly with the plot area would be my difficulty there though.
Howdy All!
I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.
The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).
However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.
Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?
I've tried using trend lines, but they don't actually average...so they're not very helpful.
Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...
Thanks for the help.
Hi,
I have to update an area chart on a monthly basis(starting from say Apr-05 to Feb-06) . The X axis range along row is (B1:L1). The y axis values (y1,y2,y3) are linked to another workbook from which the y values are pulled out.the range being (B2:L2),(B3:L3)and (B4:L4) along row respectively. When i update the chart automatically, it dips at Mar-06 as there are no y values entered. Since this excel is an analysis of monthly forecast, there is no data for mar-06. How do I prevent the chart from dipping? I want it to stop there and not dip down to zero as there is no value available for mar-06.
P.S. I have taken a sample chart for explanation and entered y values manually as I am not authorized to share complany data. Kindly provide solution for this sample sheet data.
I have data on sheet1 (attached). How can I create a normal gaussian distribution chart using vba macro in Excel of based on the column selection on sheet1 and put the chart on sheet2?
I found some link in this forum on how to create gaussian distribution in Excel:
http://tushar-mehta.com/excel/charts...tion/index.htm
http://www.exceluser.com/explore/statsnormal.htm
but if it is possible to create those above charts automatically using macro, I would appreciate if someone could share the code to me..
Regards,
WiD
Hello
I have a chart that updates itself via a dynamic range. Is it possible to add a scroll bar to this to let the user define how much data is shown on the chart?
Thanks
Simon,
I am working to update the series source in a chart. I have an excel
template in which I copy 2 worksheets into my workbook.
I would like to edit one chart using my VBA code. I am getting Method
PlotArea of Object '_Chart' Failed.
The code is below.
Thanks for your help.
Sheets(1).Select
ActiveSheet.ChartObjects("WhatMatters").Activate
ActiveChart.PlotArea.Select
Is it possible to create a dynamic chart which will accommodate the addition of an unknown number of additional dynamic series using Excel 2007?
That is, I have a db query feeding a table and there will be new rows added each day AND new columns:
............Employee 1.....Employee 2.....Employee 3.....Employee 4.....Employee 5
Jan 1.....10....................5
Jan 2.....9......................1
Jan 3.....12....................11..................8.....................55
Jan 4.....14....................12..................10...................1...................1
Jan 5.....19....................13..................13...................11.................10
Jan 6.....12....................15..................16...................16.................18
As you can see, there is more than one series and new series come on line all the time as new employees are added.
I can easily define the dynamic range:
rowscell=counta(a2:a9999)
columnscell=counta(b1:b9999)
myrange=offset(mysheet!$a$1,0,0,rowscell,columnscell)
However, when I try to use myrange as the graph data range I get a "The formula you typed contains an error" dialog.
Any suggestions?
