Move rows that match condition to different sheet with macro

[vanek]

Hi,

I’ve got a calc sheet of data with date columns. Each day the sheet grows with one row and I need to put the last 30 days data on a chart.
Since named ranges and functions as data source for charts are unsupported, I’m not quite sure how to achieve this.
I want to avoid manual deletion of rows, or modifying the graph data source field daily.

Is there a way to move rows that are older than 30 days (column A is ISO format date, like 2018-12-31) to a different sheet? I don’t want to copy them, I want to move them completely - I guess that way I can keep 30 days of data presented.

I never tried macro in excel/calc so not sure how to do it.
Or if named ranges can be achieved for charts, please do let me know.

Thanks!

[mauricio]

If you attach a file with exactly you need, I help you with this macro.

[erAck]

This doesn’t need a macro to move data. You can do this with a formula that displays the last 30 values of a column. Assuming your dates are in column A (not more than 999 rows in this example) and corresponding data in column B, enter in (cells are just examples, you can do that anywhere but need to adapt the $F1 reference)

• F1: =LOOKUP(2;1/A2:A999;ROW(A2:A999)) => result is the maximum row occupied with dates
• F3: =A1 => the dates’ column header
• G3: =B1 => the corresponding data’s column header
• F4: =OFFSET(A1;$F1-30;0;30;1) entered as array formula (Shift+Ctrl+Enter) => last 30 dates
• select F4:F33 and copy to clipboard
• move cell cursor to G4
• paste => result is corresponding last 30 data, note that the formula’s reference A1 was adjusted to B1
• select F4:F33 and apply your date format
• if needed select G4:G33 and apply your data format
• place cell cursor somewhere on data and create chart

[mariosv]

I think a couple of ways to do it.

Using standard/advanced filter you can define a range where to send the filtered data, and then do the chart on it.
Standard Filter

Using a pivot table, last versions support chart on pivot table, so no need to redefine ranges for the chart.
Creating Pivot Chart

[vanek]

Thanks for all the suggestions, I ended up with a different solution where in separate columns I just do vlookup on past dates and copy their values, and build the chart on these fix columns.