Updating Series Formulas For Charts
Jan 19, 2013
The charts I received have many Series formulas that reference and external spreadsheet
Is there a way that I can modify them and reference the same sheet name but locally
For your information
When selecting menu Formulas and then "Show Formulas" chart data series do not display
When selecting a Chart the "Show Formula" selection is not available (at least I did not find it)
In addition, when selecting the Chart, Then Design, then "Select Data", it replies in the pop-up with the comment "The data range is too complex to be displayed...."
There are about 40-60 formulas as shown below!!
The sheet name with data is D_Brand_Q
HTML Code:
' here is a sample of the current formula
=SERIES('C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$1,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$E$197:$E$218,'C:2011-12-Jul-Nov[2011_06_Sales.xls]D_Brand_Q'!$CC$197:$CC$218,2)
' This is the desired formula
=SERIES(D_Brand_Q!$CC$1,D_Brand_Q!$E$197:$E$218,D_Brand_Q!$CC$197:$CC$218,2)
View 1 Replies
ADVERTISEMENT
May 23, 2008
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
View 12 Replies
View Related
Sep 8, 2006
I have a worksheet containing 16 chart objects. The source data for each of these charts is located on various other worksheets (all within the one workbook however). Whenever I change data on my Data Input worksheet, the source data is correctly being recalculated, however, the associated charts aren't changing.
The only two ways I can get the charts to update are: (1) save and re-open the file (saving doesn't fix it but re-opeing the file appears to do a full recalculation which then correctly updates the charts); or (2) click on each of the charts individually, go to Source Data and re-accept the data range that is already in there.
I've used the following VBA on my Data Input worksheet to try to do a full spreadsheet recalculation every time I deactivate it but this doesn't solve the problem either.
Private Sub Worksheet_Deactivate()
Application.CalculateFullRebuild
End Sub
I've also tried CalculateFull and just Calculate in the VBA but to no avail.
My calculation is set to automatic.
I don't know if its relevant or not but this spreadsheet uses a custom worksheet function to derive some of the source data. I've never come across this problem before. Does anyone have any ideas on why its doing this and more importantly how to fix it?
View 9 Replies
View Related
Nov 13, 2008
i am trying to add 50 new data series and it takes hell of a lot of time to do it manually.
Sub Macro6()
Dim total As Integer
Dim Taper As Integer
Taper = 2
Do Until total < 100
total = total + 1
Taper = Taper + 1
ActiveSheet.ChartObjects(" Chart 3").Activate
ActiveChart.PlotArea.Select
ActiveChart.ChartType = xlBubble
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection("Total").XValues = "=Data!R" & Taper & "C6"
ActiveChart.SeriesCollection("Total").Values = "=Data!R" & Taper & "C8"
ActiveChart.SeriesCollection("Total"). Name = "=Data!R" & Taper & "C9"
ActiveChart.SeriesCollection("Total").BubbleSizes = "=Data!R" & Taper & "C7"
Loop
End Sub
View 4 Replies
View Related
Aug 28, 2012
I have just been asked to update a file on a quarterly basis which contains a large number of charts based on the performance of various funds against their respective benchmarks. In column A I have dates going back to 2008 and then the fund and benchmark figures in the next columns. Each quarter there will be a need to update the charts to the new rolling 3 year period, i.e. in October when I update the file, the charts will need to show the results from 30.09.09 to 31.09.12
As there are 20 charts this can be quite time consuming? The charts sit on the same tab as the data, under the columns at the bottom.
View 2 Replies
View Related
Apr 30, 2008
For some reason, the charts in my excel workbook do not update automatically when the data are changed. I have set Calculation to Automatic, but it still doesn't work. They update only if I close and reopen the workbook. Is it due to a problem with setting? How can I get the charts to automatically update?
View 4 Replies
View Related
Mar 11, 2014
I have a chart with coloring for specific series sets. When I create a similar chart, can I copy the formatting from one range to the other? Why does it default to certain styles?
View 1 Replies
View Related
Sep 10, 2012
I have a combination column and line chart with the line series on the secondary axis. Is there a way that I can tell excel to scale the secondary axis so as the line always appears above the columns? As it is it appears above in most instances but sometimes appears below the columns.
View 2 Replies
View Related
Oct 22, 2007
I have used named ranges to make a chart automatically adjust to a changing list of products in one column, but I do not know how to do this with multiple series. If I want to add products to this table, how can I make my chart dynamically adjust multiple series?
So the chart range is currently E5:H10. But I would need it to go beyond row 10 when I add new items.
Sheet1
*EFGH5*Week 1Week 2Week 36Product 11,234,123 1,258,805 1,283,982 7Product 21,234,223 1,258,907 1,284,086 8Product 33,423,321 3,491,787 3,561,623 9Product 4342,121 348,963 355,943 10Product 52,343,424 2,390,292 2,438,098
Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Dec 8, 2008
I have a xy chart with two sets of data, New and Reference. I want New to be the first entry in the legend and to show up on top when the two lines overlap.
View 9 Replies
View Related
Oct 5, 2006
i have a chart on a worksheet, it contains multiple series (3) in which i wish to change the values/ range of each series of the chart through vba. Thus far i have struggled due to the lack of intellisense, in which i tried
Worksheets(1).ChartObjects(2).Chart.Series("Fails").Values = Range("b12:b14")
with "Fails" being the name of that particular series.
View 3 Replies
View Related
Jan 19, 2008
Is it possible to independantly change the width of donut chart series? I want the outer ring of the donut to be slimmer than the inner ring.
View 4 Replies
View Related
Apr 21, 2008
I just joined ozgrid and this is my very first post. I have read the rules but spare me if I make some errors.
My question is related to plotting time series charts using VBA. I have data in the following format.
04/21/08 9:04:47 4.5055
04/21/08 9:04:48 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:50 4.5055
04/21/08 10:05:00 3.5055
04/21/08 10:05:32 3.5055
04/21/08 10:05:40 3.5055
04/21/08 10:05:48 3.5055
04/21/08 10:05:51 3.5055
04/21/08 11:07:59 5.5055
04/21/08 11:09:00 5.5055
04/21/08 11:10:01 5.5055
04/21/08 11:11:02 5.5055
04/21/08 11:13:08 5.5055
04/21/08 11:14:11 5.5055
04/21/08 11:15:16 5.5055
The column on the left is NOW() output and the one on the right is data corresponding to the given date and time. You may interpret the above data as snapshots taken at regular time intervals.
Now what I want to do is make a chart with the above data. Where ever there is a break in the snapshots, represented by a blank line, I want the graph to jump to the next data point much as the way stock prices move from the closing price to the opening price on the next day. I am not able to figure how to do that. Since I want the x-axis to represent time, using scatter charts does not work since the intervals are not proportional to the time then.
View 9 Replies
View Related
Jul 5, 2006
The question is pretty much described in the post title. Here is the situation:
I have a workbook that has data plugged into it manually throughout many sheets, and at the end there are a few sheets with different graphs and charts that are generated by a macro. This macros works perfectly - all the data is accurate and the figures are perfect when compared with a manual count. The problem I have is that the cells where the macro puts the summed values are in a chart that is supposed to create a bar graph. Normally, if I change a value that a bar graph uses I see the bar graph change - but that does not happen with the cells that have values placed in them by the macro. I also have a section that sums the values in the chart to make sure it's running right, and these values do not update automatically as well. The cells have the formula =SUM(B3:B12) and so on, in them. Right now, every time that I run the macro I need to go into the cell with the formula, hit 'enter', and then it does the summation. Is there a way to correct this problem, or is this something that is part of excel?
View 4 Replies
View Related
Nov 18, 2008
I have a chart with 3 series':
Series1
Series2
Series3
The chart is called chtEngagement. I want to use vb to update these series'.
Series1 should be shown as:
View 5 Replies
View Related
Feb 22, 2013
Any way to be able to toggle (checkbox) a particular series in a chart.
I found this old thread that shows a simple method: [URL]
BUT, I do not want the series to appear in the legend if it is not plotted.
View 1 Replies
View Related
Oct 27, 2006
I would like to display the latest data label value for a data series So if I update my table for period 2 the chart should update to display only this value. Then period 3 and so on Workbook attached
View 3 Replies
View Related
Jun 1, 2007
I am applying for a job as an executive assistant and before the interview I have to complete a "test." I am having trouble with one direction. Here is the question:
"10.Apply series name and legend key in data labels to the Star data series. "
I have included what I think is the correct answer, but I am not sure. It is under the column chart sheet.
View 3 Replies
View Related
Apr 24, 2014
I have a chart with 5 different series. Instead of editing each one, say the marker size, is there anyway I can change the marker size for all of the different series at the same time?
View 1 Replies
View Related
Apr 23, 2003
I'm trying to use VBA to automatically change the data displayed in an excel chart. To do this I count the number of items displayed on the chart using Chart.Seriescollection.Count. I then loop over the Chart.Seriescollection(x) and try to change the Chart.Seriescollection(x).Formula string to link to the cells I want.
Problem is that, sometimes, the cells the curve is currently linked to are empty. Such a curve does show up in the Count, but I can't access the .Formula, even though this can be done manually from within Excel.
View 9 Replies
View Related
May 22, 2014
I am trying to read a value in cell A1 and make say cell B1 read the same. The data in cell A1 is a real time percentage value that gets updated every lets say 10 secs. Every time the data is updated the new value goes into the next cell A2, A3, A4..... and so on. Every time it gets updated (i.e into A1,A2,A3,A4.....and so on), I want to read the new value each time in B1.
View 9 Replies
View Related
Nov 3, 2011
been trying to do some more sophisticated charts in my excel sheet. I would like to be able to write some formulas, e.g divide one arnge with another and show the result in the graph.
View 2 Replies
View Related
Oct 10, 2007
I've a lot of formulas (made in vba) in a Sheet. The problem is that even if the formulas all work as they should from the beginning, somtimes when I'm running some other macros, the formulas instead shows some error message. When selecting the cells and pressing enter, the formulas once again shows the right answer. Since it's extremely inefficient to select and press enter at each cell, what I need is some way to update these cells. Anyone know a quick way to update all formulas in the sheet?
View 6 Replies
View Related
Mar 16, 2007
I have inherited a very complex spreadsheet which has some very simple formulas. As I changed some of the variables I noticed that the cells with the formulas are not updating automaticaly. If I highlight the cell and then position my cursor inside the formula bar then hit return the cell with update. Is there a protection or setting that I am unaware of that controls this behavier?
View 3 Replies
View Related
Jun 25, 2008
I'm using a heavy excel file and I have linked some cells in one sheet to other worksheets in the same file. My problem is that when I change the value in some cells they do not change in the other cells linked to the ones I have modified.
View 7 Replies
View Related
Feb 22, 2008
I have the same question as described in Graphs Not Updating When Source Data Updated; however, the poster did not reply to the request-for-clarification questions.
My chart does not always update when my source data changes. My Calculations setting is Automatic (not Manual). There is no macro code working to produce the source data or the chart. My chart is on the same worksheet as my source data. The source data is simple links to other cells in the same workbook that update as the underlying data is updated. Saving doesn't work. Manually calculating (F9) doesn't work. The only way I have found to get the correct data to display in the charts is to open the Source Data and re-enter the Series range. Obviously, that gets old.
View 7 Replies
View Related
Jan 21, 2014
I need to 'fill down' a series of formulas in several columns - but the number of rows I need to fill down will vary dependent on my raw data.
See attached file.
Worksheet 1 - contains the raw data which is copied and pasted in from an external source. In this example, there are 20 records.
Worksheet 2 - brings in the raw data from worksheet 1 and does some calculations. The formulas are in the first 30 rows - in case the number of records grow. But what happens if the number of records is, say, 40?
I have a much bigger workbook in reality - so having too many formulas will increase the file size.
Simple example.xlsx
View 3 Replies
View Related
Aug 29, 2012
A line graph is using a data row to plot the line; works fine. The data series ranges have been named for Week1 (H10), Week1_2 (H10:I10), Week1_3 (H10:J10), Week1_4 (H10:K10), Week1_5 (H10:L10), Week1_6 (H10:M10), Week1_7 (H10:N10), ...etc.
This is the data series formula name example to plot the data series from week 1 through week 7; ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7.
The series name formula Q3Wk1_7 for week 1 through week 7 is =$H$10:$N$10.
When this name is manually entered as a Series Value for the line graph, the graph plots exactly the way it is expected. The same is true if the Series Value is manually modified for some other ranges such as Week 1 through Week 9 (='Product Plan Q3 Week 7.xlsx'!Q3Wk1_9).
My challenge is to enter this Series Value based on the manually entered week number elsewhere; i.e., we're in week 7, the Series Value should be ='Product Plan Q3 Week 7.xlsx'!Q3Wk1_7. I've been trying to use an HLOOKUP table to select the correct Series Value formula based on the week number. The HLOOKUP works fine resulting in selecting the correct formula. I can also Name the range of data I want to graph and copy the name into the Series Value field with successful results. How do I get such a formula or Named range into the Series Value field without type it in? I want to set that Series Value to a range or name without having to select the line to manually enter the value.
View 3 Replies
View Related
Jun 27, 2013
My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers
Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
View 2 Replies
View Related
Aug 4, 2014
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
View 1 Replies
View Related
