Dynamic Chart Trouble

[droscoe87]

I'll admit I'm probably doing things the hard way, but I've accomplished so much in my project so far. I've google literally for hours, I've downloaded several sample worksheets, worked through examples on websites, messed with named ranges. I assume the malfunction is on me, of course, but nothing is working for me. I have a button that records the values of several cells and copies them to another worksheet, where each row of copied values corresponds to a date/time when the user pressed the 'log' button. My sheet structure is as follows:

Sheet - Details
(user input and log button that copies specific cells into Log2 sheet)

Sheet - Log2
(500 empty rows, columns divided into various metric categories, ready to be populated with the copied values from the 'Details' sheet, empty to begin with)

Sheet - Tracking
(An overview of total progress, which is up and running just fine, and also a section where the user can see their stats from the last 10, 20, or 50 logs)

Categories Under Examination (all stored on Log2 sheet)
Col A - Date & Time
Col BR - Focus Rating
Col BT - Profit / hr

I want a line chart comparing the two values, with the dates as the horizontal axis. Conceptually, a higher focus rating would correlate with a lower hourly profit, which is the point of this particular graph. The scale is wildly different between focus rating and profit/hr, so Id move one to the secondary axis. But the chart has to be able to update whenever a user logs new stats!

When the user first uses the excel file, the Log2 sheet is empty. As they log their progress, Log2 sheet is populated and I want the charts on 'Tracking' to populate as more info is logged by the user. Since the stats on Log2 will be empty to begin with, the charts won't have any plotted points on them yet either. More importantly, I want the user to be able to define the 'range' of the most recent log times that they can view on the chart. Maybe they want the chart to display 10 of the most recent logs, or 20, or 50. I will have a predefined list of options of 'log ranges' for them to select from a dropdown.

In short, I can't work out a dynamic chart that populates as data is made available and is able to plot the X most recent points of info. (X being user selected from a list of range options).

I apologize if my explanation is making things more difficult. Thank you for your time.

-Dustin

 

[droscoe87]

I apologize for making this difficult, but one thing would remedy my problem I believe, so perhaps we should start there with a solution? I have the following code:

Private Sub LOG_Click()If VALIDATEDATA Then Exit Sub
Range("S63").Font.Color = RGB(0, 176, 240)
Dim LR As Long, i As Long, cls
cls = Array("DN63", "Ship", "Internal_1", "Internal_2", "Internal_3", "Internal_4", "Internal_5", "Internal_6", "Internal_7", "Internal_8", "Internal_9", "Internal_10", "Internal_11", "AF50", "AF54", "AF59", "AF46", "T56", "Z56", "O56", "AF15", "AY15", "AF18", "AY18", "AF21", "AY21", "AF24", "AY24", "AF27", "AY27", "AF30", "AY30", "AF33", "AY33", "AF36", "AY36", "AF39", "AY39", "AF42", "AY42", "DT24", "DS16", "CK20", "CK25", "CK30", "BU75", "CS15", "CS20", "CS25", "CS30", "CV36", "CS36", "CV38", "CS38", "CV40", "CS40", "DA40", "AT58", "AW58", "BG58", "BN58", "AT59", "AW59", "BG59", "BN59", "AT60", "AW60", "BG60", "BN60", "CA52", "CM50", "CM56")
With Sheets("Log2")
    LR = WorksheetFunction.Max(1, .Range("A" & Rows.Count).End(xlUp).Row + 1)
    For i = LBound(cls) To UBound(cls)
        .Cells(LR, i + 1).Value = Me.Range(cls(i)).Value
    Next i
    Range("S63").Font.Color = RGB(13, 13, 13)
    MsgBox "Mining Info Logged!"
End With
End Sub

It copies the specific cells, and pastes them into Log2 horizontally, across one row, and then moves down to the next empty row.

Is it possible, to have it paste the values in the row horizontally, but then INSERT an empty row above that row it just filled out? This would, by default, order my list from most recent to oldest. I can simply select the top 10 rows as my chart data and it would automatically update the chart points as Log2 is filled out...as long as the new values were pasted ABOVE the last row of values.

make sense? Thanks again.

 

[Andrew Poulsom]

Maybe this will help you:

Dynamic and Interactive Charts