Interval-Y In Duplicate Chart Go Offset When Data Update
Mar 2, 2014
I have two charts (Chart A and B) and 1 duplicate chart of chart A ("Chart A", didnt change the change name so remain the same).
"Chart A" is overlay above Chart B to see the actual different.
When the data is updated, interval-Y in "Chart A" is different (by +-10% offset) than Chart A which support to be the same.
At this stage, comparison is not possible.
The interval-Y is link to vb.
Checked when there is offset the maximum and minimum offset is different and else I cant find any other problem.
Do I have to state that there are two Chart A in the vb?
View 1 Replies
ADVERTISEMENT
Apr 13, 2012
I have got a lot of data to put in a chart that updates every 5 minutes with new data - i have a chart that Line chart that shows this.
It shows the cost of something every 5 minutes.
I have put this in a chart using offset but obviously i want to show the cost over the course of the day - because this updates every 5 minutes, the axis at the bottom (which has the time of the data) is massive and doesn't look good.
Is there a way to show the axis at an hourly interval but keep all the data in the chart?
View 5 Replies
View Related
Apr 28, 2014
I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
QQ截图20140429104406.png
View 1 Replies
View Related
Oct 16, 2008
I have a template whereby it show agent hourly performance. I unable to use vlookup formula because duplicate id with different interval. If I select id 1977 it will auto update agent performance it the table according to the interval.
View 9 Replies
View Related
May 9, 2008
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:
...
C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...
E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...
G9 111568 298021 12940 31645 181797 36996 ... ....
...
I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?
Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly.
View 9 Replies
View Related
Dec 14, 2012
I am making a macro to update the data on a bunch of charts. This is what I got from the macro recorder when I selected the data manually.
Code:
Sub testthree()
ActiveWorkbook.Charts("Julian's Words").Select
ActiveChart.SetSourceData Source:=Sheets("Super Sight Chart Data").Range( _
"C1:H1,C5:H5"), PlotBy:=xlRows
End Sub
I have created the part of the macro that will go to the data table and define the two ranges I need to use.
Code:
Sub Adjust_Graphs()
Dim lastdate As String
Dim daterange As Range
[Code] .....
However, this code is selecting the entire range C1:H5. How can I get the code to take JUST the two lines I need?
Exmaple: The X axis needs to be C1:H1, and the Y axis needs to be C5:H5.
I also tried this, without success:
Code:
Dim combrange As Range
Set combrange = Union(daterange, wordrange)
ActiveWorkbook.Charts("Julian's Words").SetSourceData Source:=Sheets("Super Sight Chart Data").Range( _
combrange), PlotBy:=xlRows
View 1 Replies
View Related
Apr 12, 2006
I've been updating all the charts manually each time I update the entries in the worksheet which charts are created from in the same workbook. Is there any way (eg. by use of macros or VBA) that I can have all the charts updated automatically when data ranges are updated each time?
View 2 Replies
View Related
Aug 19, 2006
I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..
I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).
The new row will be added each time right below the header, right above the
most recent data given. So a row will be added above row 8!
View 9 Replies
View Related
Apr 28, 2009
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies
View Related
Jul 22, 2007
Attached is a workbook that I am having a major problem with. Basically it is a stock management system for a hotel allowing purchase orders to be easily generated and receipted.
The purchase side is working as I want it to - so no problems there.
On the receipting side, however, I am having a lot of trouble. Here's what I want it to do.
For each item shown on the Receipt sheet that has a quantity in the received column, I want the code to find the appropriate line in the StockTotals sheet (using the UID which appears in column A of both sheets), then offset the appropriate number of columns, and update the numbers as follows;
Stock on hand grows by the number receipted.
Number on order drops by the number receipted.
If the number on order is now zero, then I want to clear the ordered cell as well as the ordered date cell next to it.
View 9 Replies
View Related
Jun 20, 2007
Here's another question for you excel junkies to solve, while I try to understand what y'all are doing. I have two worksheets named "MASTER" and "BEDS". What I need to do, in sheet "MASTER" is go down column ("K:K"). "K:K" has unique data in it. then for each row...
"X" .value = offset minus one
"Y".value = no offset
now I need to go to sheet "BEDS" and find "Y" in column "A:A" then
from "Y" offset + 3.value = "TRUE"
"Y" offset +4.value = "X"
there should only be one instance of "Y" in column "A:A"..............
View 3 Replies
View Related
Feb 21, 2008
I have a list of about 1000 names with 2 columns next to it. The first contains a list of different groups that the names belong to (there are 19 possible groups). The second contains a list of figures ranging between £10,000 and £500,000. I want a formula that will look for a name in the list and, when it finds the name, return the corresponding group and corresponding amount. My problem is that some names are in more than one group and therefore appear in column 1 more than once. I have tried to use the LOOKUP formula [eg looking for the GROUP =LOOKUP(Sheet1!B33, Sheet2!A:A, Sheet2!B:B)] but when a name appears more than once I cant get all the results - for example:
Mr Phil LittleGreen Group35,000
Mr Ed GrantRed Group20,000
Mr Robert SmithOrange Group10,000
Mr Robert SmithRed Group50,000
Mr Robert SmithGreen Group75,000
Mr James MorrisonGreen Group100,000
On my summary page (Sheet1) I would like to type in Mr Robert Smith (into cell Sheet1!B33) and in the rows below (obviously in 9 different cells) have the results appear but I can only ever get Mr Robert SmithGreen Group75,000 as a result. This is my first post so I hope I have obeyed all the rules and this is clear
View 5 Replies
View Related
Dec 6, 2006
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
View 2 Replies
View Related
Feb 7, 2014
I have geological readings "W,X,Y & Z". For every drilled hole I have these readings recorded for individual intervals (i.e. 1-2m, 2-3m, etc.)
However, the intervals have now been grouped together based on other (unlisted) specifications.
Now I am tasked with averaging these readings "W,X,Y & Z" for the decided groupings. For example.... if intervals 1-4m have been grouped for the first hole, what is the average reading of "W,X,Y & Z" over this new interval?
I have attached a file which displays the layout of the spread. To the left is the original data, and in columns I:K are the new 'groupings' of intervals. Ideally, I would like to generate the average table to the right of the original data (highlighted in green).
So basically, I need some vba code to average the readings in the 1st table conditionally to correspond with the grouped intervals in the 2nd table.
View 1 Replies
View Related
May 28, 2012
I'm trying to rearrange vertically aligned data so that they are horizontal. But at the same time, I need this macro at an interval.
To give you an example,
Mr. Yang Xin 221-421-5123 Male College Grad
Ms. Taylor Cindy 534-123-5512 Female Uni Grad
In this case, we need the data to be arranged this way.
Mr. Yang Xin 221-421-5123 Male College Grad Ms. Taylor Cindy 534-123-5512 Female Uni Grad
Could you write me a macro?
View 5 Replies
View Related
Mar 31, 2014
I have a column with intervals(every 30 min) and with some values. My problem is that if there is that if the value is 0, the interval is deleted. Is there a way(maybe macro? ) for the excel to see that an interval is missing and add it with value 0?
Attached File : intervals.xls
View 9 Replies
View Related
Apr 18, 2009
I have 2 columns of data (value and time):
for example:
15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00
They are continuous data. so I want to divid the value into hourly data as follows.
15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00
View 9 Replies
View Related
Jan 27, 2010
I got 2 fields A2 = Date1 and C2 = Date2
The Date1 field comes from a dropdown list using Data Validation
I want Date2 to also have a dropdown list but to be greater or equal to Date1, is this possible?
Check the following example
View 8 Replies
View Related
Mar 14, 2012
I have an Excel Workbook with data in Sheet1 and a chart in Sheet2.
I use the OFFSET function to define the data range. In Sheet2, I have 3 cells, B2(column), B4(row) and B6(period). I have slider to adjust the values in B2 and B4.
I define the data range at follow.
Xchart =OFFSET(Sheet1!A1,Sheet2!B4,0,Sheet2!B6,1)
Ychart =OFFSET(Sheet1!A1,Sheet2!B4,Sheet2!B2,Sheet2!B6,1)
When I tired to put in this into value series
=Book1.xlsx!Ychart
The error message ...
Reference not valid. Reference must be to an open Worksheet.
View 3 Replies
View Related
May 28, 2014
I am trying to create a dynamic Excel line chart.
All is well, except for my x-axis (which is a text axis). It currently looks like this: Dynamic axis.jpg
I am using the Offset formula. The formula for my x-axis is =OFFSET(Worksheet1!$D$4,0,-3,COUNT(Worksheet1!$D$4:$D$36)). This is to show the axis if cells in Column D are filled up appropriately.
However, in the cells containing the axis name itself, this formula is used =IF(D9=0,NA(),'Worksheet2'!A6). I've tried removing the formula from these cell, but that didn't solve the problem.
View 5 Replies
View Related
Feb 15, 2010
I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)
View 4 Replies
View Related
Feb 16, 2012
I am using a VBA macro which performs a Yahoo webquery to obtain stock data. After the data are downloaded, a chart is updated with the new data.
The current macro updates the scaling on the price axis just fine, however, if I change the time frame the x axis, i.e, time/date axis is either spread out to far or jammed up.
The code for re-scaling the the y axis is shown below:
Code:
Sub UpdateScale()
Dim ChartVar As Chart
Dim lMax As Long, lMin As Long
On Error GoTo ScalingProblem
'Assigns the values in the Min and Max ranges to variables.
[Code]....
Any similar sub that would also rescale the x axis as needed when the data intervals are changed?
View 6 Replies
View Related
Sep 5, 2008
I have used the function = now() to have the most updated time but it updates a workbook when I open it in the first place. How I can avoid this?
View 9 Replies
View Related
Feb 21, 2014
I had a couple of days ago..
I can get the "Daily %" to update just fine but for the life of me cannot get the "Total Number of Patients" to update.
View 3 Replies
View Related
Feb 11, 2009
I'm trying to make a scatterplot so what I did was recorded a macro and now I want to be able to update it using a for-loop. Here's my code.
View 4 Replies
View Related
Aug 30, 2007
I'm trying to change the source data for a worksheet for a series of charts. The source data appears in a series of tables. There are about 30 such tables and associated charts on the worksheet. (I need to duplicate this many times over, which is why I need to automate.). The chart names seem to increment by 2 (i.e. Chart 1, Chart 3, Chart 5) And the source data for the charts increment by 9: BM95:BP102, BM104:BP111, etc. How can I automate this? Also, I'm trying to automate changing the reference in the title of the chart. This reference increments rows by 9. When I use the macro recorder to do this, I get the following
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SetSourceData Source:=Range("BM95:BP102")
I'm unable to generate any code that allows me to change the reference of the chart title.
View 4 Replies
View Related
Mar 17, 2008
I have a macro that extracts data from a source workbook. The data is owerwritten every time that I use the macro. Now I have a graph that everytime I use the macro does not update with the new data, Is there a clever way to do that? The code for extracting from the workbook is the following:
Sub Extract()
Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
ActiveWorkbook.Sheets("CURRENCIES").Select
ActiveWindow.SelectedSheets.Delete
myFileName = Application. GetOpenFilename("Excel files,*.xls")
If myFileName = False Then
Exit Sub 'user hit cancel
End If................
View 4 Replies
View Related
Aug 31, 2007
I'm trying to create a chart and set its source dynamically.
I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,
ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))
And by using the chart wizard.
Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")
Setting the XValues property gives an "Unable to set the XValues property of a series class".
Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.
I've also tried
resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring
When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.
Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.
View 7 Replies
View Related
Jul 18, 2014
I want have a chart that use Formula result to update automatically. I know there is some vb methods to do this but my job is different and that ways work when values enter manually but i need a way that use formula result to update and don't make chart as long as existing values. I attached a sample of i need and i did explanation on it. The Salesman's are enter to column A by a VB and Their values of cost or earning are enter by a formula.
View 2 Replies
View Related
May 2, 2008
I am having difficulty figuring out how to get my chart title to automatically update when my chart changes via filtered criteria. I can get it to change by clicking the title bar on the chart, but would like the title to automatically update as soon as I select new criteria and the chart changes.
View 9 Replies
View Related
