Excel Pivot-Chart : show last 25 lines

Question

I have a set of data and i have a macro running adding a line each 5 seconds This is linked to a pivot-table and a pivot-chart in a Dashboard

Looks like this (picture is my 2nd version with incremental instead of decremented)

I have a field in a hidden page where i put how many of the last logged line i want to see

For example i have 1000 lines and i want the dashboard graph, to the press of a button, toggle between "see only last X" or "see all"

I want to find a lighter way to do what i have done cause i see a big latency

I have achieved this by adding two columns in my data with a macro writing formula in them in a way that

Col D : give a value to the line

Col E : if the value of D > 0 write "last X logs" and the rest "before X logs"

I put this code for Col D

Settings!I4 = Field where i but the amount of logs i want Ex; 15

Settings!I5 = Is where i store the last row value to be used in the formula thus updating all the line at each added lines

setsheet.Range("I5").Value = lastrow

logsheet.Range("D" & lastfreerow).Value = ("=Settings!I4-(Settings!I5)") & "+" & setsheet.Range("I5").Text

This write the formula in the cell and look like this :

=Settings!I4-(Settings!I5)+7

the result is a count down where the most resent value is "15" and the oldest goes in the negative

And put this code for Col E

logsheet.Range("E" & lastfreerow).Value = "=IF(RC[-1]>0,""Last"" & "" "" & Settings!R4C[4] & "" logs"",""Before the last"" & "" "" & Settings!R4C[4] & "" logs"")"

This write the formula in the cell and look like this :

=IF(D8>0;"Last" & " " & Settings!I$4 & " logs";"Before the last" & " " & Settings!I$4 & " logs")

And i use those those "values" in the slicer that sort my pivot-table

thank you

Paste the complete code and some sample data, otherwise is hard to tell how to improve it — Ricardo Diaz, Oct 04 '19 at 01:05

score 0 · Answer 1 · answered Oct 04 '19 at 22:37

This is my version a bit simplified since there is only one column with a updating formula at each cycle. But i didnt see much improvement on the load of the macro

Before

        'Create the IF formula that will give a value to the row, the older the row the smaller the value
setsheet.Range("I5").Value = lastrow

logsheet.Range("D" & lastfreerow).Value = ("=Settings!I4-(Settings!I5)") & "+" & setsheet.Range("I5").Text

        'Create the IF formula that will say if the row is part of the desired range for the "last logs" option
logsheet.Range("E" & lastfreerow).Value = "=IF(RC[-1]>0,""Last"" & "" "" & Settings!R4C[4] & "" logs"",""Before the last"" & "" "" & Settings!R4C[4] & "" logs"")"

After

        'Log#; simply give an incremental value to each row starting from 1
            setsheet.Range("I6").Value = lastrow - 6                                            'Row value of the last log, put into the hidden setting page to be used in a formula
            logsheet.Range("D" & lastfreerow).Value = (lastrow - 6)                             'Row value of the last log,

        'Create the IF formula that will say if the row is part of the desired range for the "last logs" option
            logsheet.Range("E" & lastfreerow).Value = "=IF((Settings!R6C9 - Settings!R4C9) < (Log!RC[-1]),""Last"" & "" "" & Settings!R4C9 & "" logs"",""Before the last"" & "" "" & Settings!R4C9 & "" logs"")"

If you have any idea of a way to sort a Table in a way that the Chart show only the last X values im interested

Thank you for your time!

Excel Pivot-Chart : show last 25 lines

1 Answers1