How can I record a current cell value (that is constantly changing) on another Excel sheet at a specific point in time?
If you think that Excel ought to be able to do this right out of the box, please consider voting for my suggestion on Excel UserVoice. The more votes the idea receives, the more likely Microsoft is to add the feature. Microsoft has been especially active in the past year with implementing these suggestions.I posted the content below from Brad Yundt's answer to Is it possible to record a figure on a spreadsheet that is always changing? For example, if there is a figure which is constantly changing due to real time data being input to it, can I record it’s value at time intervals on another sheet? I originally tried to combine that thread with this one, but was overruled by the Quora bot.Excel has the capability of scheduling a macro to run at a certain time. If that macro schedules itself to run again, say 5 seconds later, you would be able to capture real time data and record it in 5 second intervals.Here is some sample code that captures the data in Sheet1 cells A1:A5 every 5 seconds, and stores it (along with a time stamp) in successive rows on Sheet2.'This code should go in a regular module sheet, and won't work properly if installed anywhere else. _ The next (Dim) statement must occur before any subs or functions.Dim NextTime As Double Sub RecordData()Dim Interval As DoubleDim cel As Range, Capture As RangeInterval = 5 'Number of seconds between each recording of dataSet Capture = Worksheets("Sheet1").Range("A1:A5") 'Capture this column of dataWith Worksheets("Sheet2") 'Record the data on this worksheet Set cel = .Range("A2") 'First timestamp goes here Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0) cel.Value = Now cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Application.Transpose(Capture.Value)End With NextTime = Now + Interval / 86400Application.OnTime NextTime, "RecordData"End Sub Sub StopRecordingData()On Error Resume NextApplication.OnTime NextTime, "RecordData", , FalseOn Error GoTo 0End SubThe above code is initiated when the workbook is opened, and stopped when the workbook is closed by code in ThisWorkbook code pane.'These subs must go in ThisWorkbook code pane. They won't work at all if installed anywhere else!Private Sub Workbook_BeforeClose(Cancel As Boolean)StopRecordingDataEnd Sub Private Sub Workbook_Open()RecordData End Sub
Graphs in excel: can I do insets/mini-graphs that change automatically?
I am plotting some data in excel, the data plotted forms a thin loop, but it is important that I zoom in on the bit where the loop crosses the x-axis. I want to plot a graph that shows the whole loop, then on a blank region of that graph, I want to show a zoomed-in version (an inset) of the narrow region of axis that is difficult to make out. However, the only way I have done this is by zooming in and print-screening, then pasting it in paint, then getting the bit I want and pasting it back into excel. This is tedious though, and I don't want to have to do this many times (i have lots of data). Is there any way I can do something that allows me to create a proper little inset of the region of graph that I need, but done in excel so that it's done automaticallly for me? thanks.
How do I change the default decimal places on a chart in MS Excel 2007 ??
Right click on the chart axis that you wish to format. Choose Format Axis. On the scale tab there are 4 boxes. Minimum, Maximum,Major Unit and Minor Unit. If the boxes are checked they will dynamically change to the numbers necessary to make the data fit logically, to Excel. To define that the chart behave in a certain manner, unselect all of the checkboxes and define the information that you want to chart in the accompanying box. Highlight and right click on your data and choose Format Cells. Select the numbers tab and format your data as number and specify the number of digits you wish to carry out your calculations to. In 2003, by default, you can calculate to the 15th or 16th decimal, Maximum, I think?? I use Excel 2003. I am sure the charting has not changed much.
Excel #REF when copying graphs onto other graphs; Help?
REF indicates the source for the legend is missing. You would have copied the graph alone and not the table which is used to create the graph. You can do one of the following. 1. If all the figures in the X-axis and Y-axis are correct and only the legend is showing #REF, right click on the #REF and choose edit text and provide the legend name. 2. Instead of copying the graph, copy the source table and try to create the graph in the other sheet. This will be the best one as any alteration in the source data will adjust the graph accordingly. 3. You need to establish connectivity for the source data to the graph in the other sheet. This will make the graph adjust automatically when the source data is changed.
How to create an excel macro to rank data?
Hi Scott, The RANK function ignores blank cells. Why not just type this formula =RANK(B1,B:B,1) Then move the cursor to the bottom-right corner of the cell until the cursor changes to a solid plus sign, then double-click. This will automatically copy the formula down the required number of rows. If you are always copying and pasting data into this same sheet, you can use this formula =IF(B1="","", RANK(B1,B:B,1) Copy this formula down about 500 rows. This will eliminate any errors in the cells that are past the last row of data you just pasted. You can also simply click the Sort button and do a sort. If you still want a macro, here it is Sub QtrRank() 'change the B below to the column that has 'the values to rank col = "B" 'change the M below to the column where you 'want the rankings to go rk = "M" Lastrow = Range(col & Rows.Count).End(xlUp).Row 'if data starts in row 2, change the 1 to 2 For i = 1 To Lastrow Range(rk & i).Formula = "=RANK(" & col & i _ & "," & col & ":" & col & ",1)" Next i End Sub