I am trying to chart data from a single cell that is changing over time. The cell is linked to another software program that provides its value. I would like to be able to chart the changes in its value either every time it changes or on some time interval (i.e. every 3 minutes).
View 9 RepliesI am trying to do this all in Excel and not have to us VB or Access.
I made a simple form in Excel to enter the hours worked for employees.
NAME , RATE, TOTAL HOURS WORKED ...
I want to link the data from this form to drop into another list that will show
the a running total of all hours worked.
BUT
Every time I would clear the form the data will be removed from the linked
cell.
I have a master summary sheet that contains all the required information for 21 other worksheets. Each row contains the information for a single sheet. I have linked all the relative cells to the first sheet but would like create a formula that changes the row reference in each of the links rather than the tedious task of updating each individual link in every sheet.
I have tried using the indirect function but constantly get errors returned, the basic formula i have been using is: =INDIRECT("Summary"!C&,X1)
'C' is the column in the summary sheet that the information should be taken and 'X1' is the cell in the worksheet that will define what row the data should be taken e.g in one sheet the reference for that specific cell will be C5 and the next C6 and so on.
This is the current layout I have:
Sheet 1: A1:B20, is linked to Sheet 2: A1:B20.
I need to copy and paste data from the internet into Sheet 2, but it's not in the correct order, so I must swap column A and column B in Sheet 2 only. The problem with this is that when I swap them, the linked cells swap as well, which destroys my data on Sheet 1 columns C-Z.
Is there a way that I can keep the cells on Sheet 1 completely static, so that it only reads the data I put into the corresponding cell on Sheet 2?
Or maybe, is there a way I can just swap the cell information in Sheet 2 without messing around with the formatting? Just a complete swap of A1>B1 and B1>A1?
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
Example:
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
I am using Jon Peltier's worksheet_change event code to try and automate chart axes.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$AG$5"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$B$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$AG$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
.MajorUnit = Target.Value
Case "$L$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "$N$3"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case "$AH$7"
ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
.MajorUnit = Target.Value
Case Else
End Select
End Sub
However, I have some of the cells setup as formulas....but worksheet_change apparently only updates values when manually changed.
Has anyone ever seen a problem like this? The Legend text is not displaying what is in the cell reference and I cannot figure out why.
View 9 Replies View RelatedI am trying to create a macro which will allow me to change the source data for a particular chart. what i want to do is to have a code which looks in a range of cells (A8:B28) and then updates the chart to only include those cells within the range that contain a value? The number of cells with values changes depending on what options are selected elsewhere in the spreadsheet.
View 2 Replies View RelatedI'm facing a charting problem and i can not find any solution with a search here, anyway this is the problem: When I choose a week I would like to see the results of the 5 previous week also.
View 4 Replies View RelatedEach week I have to add a new column to a report and then update the source data of all the charts that use that newly added column. I am trying to create a macro that will use the last 12 columns of data located on a different worksheet as the range for the source data of the chart. I have attached an excel document that contains an example of how one of the charts that I am using looks and where it pulls the data from.
View 3 Replies View RelatedI would like to find out how can I create a chart, where it is interacting with the selection of 3 drop down list (created from data validation-list option). It will show up to 12 months of data.
First list would have only Male & Female, 2nd list would have 1st Class, 2nd Upper, 2nd Lower, 3rd Class and Pass Only, 3rd list would have all the months of 3 years.
My data is from J9:U22. These data needs to be changed accordingly, reading from D45:U58.
For the graph, if I select Male, 2nd Upper, and Jun-2012, I want it to show only this selection, where the period will be from Jul-2011 till Jun-2012.
Previously I have done one, with only 1 selection of drop down list, where I used Vlookup. Now involves 3 criterias.
Or is there an easier way to do it?
I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...
Y-Axis is the data that I am trying to present - Total $
The source of this data is simply two columns - Column A is the running date, Column B will be data.
Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).
Can a macro be used to auto update the bar charts based on today's date?
I am looking to change the color of my text through a macro, which is fine. But I want to change the color according to a date key. So every time I update my date once a week, a new series is "blacked in". The series has the link already in it, I just need it to change color (to black) every time I update it.
View 1 Replies View RelatedI'm trying to create a simplified Gantt chart of sorts, and cannot figure out one piece of it. I'm not sure how to write out exactly what I need, but here goes.
I would like to have a formula that looks at the percent completed (which the user inputs) and multiplies it by the total duration for the task to give a total number of days completed. Then, under the corresponding dates, the color of the cell would change. I've attached a simplified version of what I'm talking about.
On the attached spreadsheet, the total duration (D2) is 5 days and the percent completed (C2) is 40%. This calculates to 2 days. With that said, I would like the cells under the first 2 days under the listed dates (E2 and F2) to change color. When the percentage complete reaches 60%, then G2 would change color, when it reaches 80%, then H2 would change, and when it reaches 100%, then I2 would change.
My guess is that the formula will need to result in a particular value, and then I'll use Conditional Formatting to do the actual changing of the cell color. Assuming this is correct, I still don't know what formula to use to accomplish this.
I receive data for time windows on jobs. my issue is 10-11 time frames appear before 8-9 time frames when I move data into a Pivot Table. This happens because the data I receive does have "08" signifying 8 so the "1" in 10 causes the 10-11 to appear before the 8-9. Is there a formula I could use to make this change? Currently if I want to view items in sequential order I have to go into the file and add a 0 before any jobs that start at 8 or 9am
View 3 Replies View RelatedI'm not sure how this could be done though i'm pretty sure its going to require some VBA instead of conditional formatting. I would like cell B2 to have background colour red for 10 seconds after cell A1 is edited. So every time A1 is edited B2 will change colour for 10 seconds and then back to original formating.
View 4 Replies View RelatedMy question is regarding changing properties in a chart without activating it. My current example is with adding data labels, but there are many other instances I could use this information in. Here is my current code:
VB: ActiveChart.SeriesCollection(2).ApplyDataLabels
However, I was hoping to replace it with something like this:
VB: Sheets("Dashboard").ChartObjects("Chart 1").SeriesCollection(2).ApplyDataLabels
I get the "Object doesn't support this property/method" error. Is there a way to do this? It just seems inefficient to have to activate the chart in order to make changes.
I have a sheet with about 50-60 charts and I am hoping to have a master list of chart titles on the side, how can I link these titles with the charts themselves?
View 1 Replies View RelatedI have an Excel 2003 spreadsheet used to calculate doses of medication. The value of several cells will depend on what optionbuttons the user selects.
I have entered the first two lines of code below w/o a problem to make certain calculations that are displayed in the specified cells.
I then tried to make an additional calculation to display in cell 6,9, but it always triggers a "Application-defined or object-defined error". For troubleshooting purposes, I moved that line of code directly under the other two lines of similar code that I know work, and simplified the problem line to just arbitrarily set the value of the cell to a value of 2 (instead of the actual formula); it still triggers the error.
I have linked a graph to update from a pivot table. I would like to print a copy of a graph and pivot table for each item in the page field.
Can a macro loop through each page field and print?Is there a way to send them to the printer all at once? (I noticed the printer hangs when manually printing page by page)
I've tried "view all pages of pivottable" but then I lose the view of the chart. I tried recording the events but the number of items in the page field changes with each data update and I'm not sure how to write code to accept this.
I have a Macro 'Timenow' to give current time in a Cell of MS Excel, but then it changes all Cell values of sheet where the macro was used.
Sub Timenow()
'
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
ActiveCell.FormulaR1C1 = "=NOW()"
Selection.NumberFormat = "h:mm"
Range("F5").Select
End Sub
Even assigning value to variable does not work:
Sub Timenow()
' Timenow Macro
' Keyboard Shortcut: Ctrl+b
Dim TN As String
TN = Format("=now()", "h:mm")
ActiveCell.Value = TN
End Sub
how to restrict the macro to change the value of current/active cell only without effecting other cell values?
In my job, my team have to take phone-calls from stores asking various questions. In order to combat this, I have set up a call tracker to see where the calls are coming from (so we can beat them with a stick). I have asked my colleagues to record the date and time of the calls.
I need a big button at the top of the page that says 'New Call'. Then, when this button is pressed, the time and date stamp will automatically be put into cell A3, then when pressed again a4, and again a5... and so on.
I am struggling with the following:
-Using CTRL + Shift + : to input the date/time on a macro inputs the date I made the macro, not the date/time the button is pressed!
-I can only get the date to go in cell A2 - If I press it again, nothing happens. how to do this, or just do it for me
In A1 I have 35 min elapsed time and need to change it to time as a portion of an hour. It reads the 35 min as TIME, therefore I am currently using
=IF(A1>=0.5,A1-0.5,A1)*24. Output is .58, which is perfect.
(output column formatted as a number)
Likewise 3:28 becomes 3.47.
I got a set of data which is only 2 colums collecting data problems. The output i get is a time stamp and a fault. Im looking to set a chart up with the time been on the X - axis and number of problems per hour on the Y - axis.
View 7 Replies View RelatedI want to have the "category (x) axis label" to change with each new entry into column A. I would like to start from the last entry and go back 30 and have that change each time there is a new entry. I guess what I am trying to say is I only want to chart the last 30 entries.
View 2 Replies View RelatedSub autograph()
Dim x As Long, t As Long, y As Long, z As Long
y = 3
z = 5
x = 4
While x < 609
With Worksheets("cabernet (2)")
t = Cells(x, 1).Value
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("cabernet (2)").Range("B" & y & ":H" & z), PlotBy:=xlRows
ActiveChart.Location where:=xlLocationAsObject, Name:="Cabernet (2)"
ActiveWindow.Visible = False
ActiveChart.HasTitle = True
ActiveChart.charttitle.Text = " " & t
Windows("complete Favorite Genes.xls").Activate
y = y + 3
z = z + 3
x = x + 3
End With
Wend
End Sub
This time, however, instead of having a slight idea of what I need to do, I don't have the slightest of where to start. I need to move all of these charts into another sheet, sheet1, and i'd like to offset them or do something so that they aren't all right on top of each other. I'd be more specific but I don't really know exactly what you can or can't do.
I'm trying to create a chart with different source ranges e.g if i used
With ch.Chart
.SeriesCollection.Add _
Source:=Worksheets(1).Source:=Worksheets(1).Range("C1:C8,J1:J8")
End With
That works fine but I want to modify the columns it looks at. I was going to use the cells(x,y) method but I can't get it to work. icol = 3. Range(Cells(1, iCol), Cells(8, iCol)). This works for a single range(C1:C8), but how do i reference Range("C1:C8,J1:J8") in the same way.
I have a workbook, with 2 sheets. first page has graphs, second has data. in my charts I refer to a named range on the second one as follows: Data!Named_Range, where "Data" is the name of the second sheet. However when i save this, it changes the range of the chart to: '101044.xlsm'!Named_Range, where 101044 is the name of the workbook. This is a problem for me as this workbook will often be loaded into a document handling system where the file will change name, throwing out all sorts of errors because the chart path is invalid, and i have to change the range of all the charts manually. why does it have to use the file name when reffering inside the same workbook? and how do i force this to not change?
View 5 Replies View RelatedI have an issue with pivot charts that I can't figure out and I can't find anyone with the same problems either. I have several big reports that use data from 2005 onwards that need to be represented on graphs separated by the city the data belongs to and different ranges of the results. This data is being sourced from inside each workbook on another tab and turned into a series of pivot tables (for each city) which provides the source for the charts. I did it like this because if I use pivot graphs I don't have to go through and individually change the data source range in the charts every time - because there are so many.
My problem is that when I refresh the pivot tables (to bring in the new data) the pivot charts 'chart type' resets. They should be represented in a Line - Column on 2 Axes chart and I have set this as the defult type but after refreshing it reverts to a basic column graph and I have to go through and change them all back manually. I made a macro that acts on a currently selected chart to change the format to make this process a bit faster:
I have a quarterly report that measures the safety performance of the plants in our company using bar charts. One set of 4 charts for the company as a whole and four additional sets of four for each of our divisions for a total of 20 charts.
I would like to change the color of the bars based on whether the location has met (bars colored green) or failed to meet (bars colored red) the goal for that criteria. The company average will be a yellow bar. Of course it can be done manually but that is really slow and tedious.
Is there a way, using VBA or a macro, to change the color of a bar (data point) based on the value of that data point? Can it run through all of the data points in each chart and make those changes based on criteria that I can set for each type of chart?