Copying columns of data from one sheet to another based on multiple criteria

As you're new to spreadsheets, I would point out that much of what you do when collecting data and reporting on it will depend on data quality and field standardisation.

For example, in the Risikolog sheet, rows 3, 4 and 5 has data missing in them, such as the Rapport. This means the records in these rows would never be picked up in the Afrapportering sheet because they do not have a year against them - just as they would not if you applied a filter in the Rapport column in the Risikolog sheet.

Another example is that the Risikolog sheet has the years as 2016, 2017 and so on, but your example of filtering by year proposes a format of "2015_2016" to mean "in either 2015 or 2016". If you intend using the underscore (_) between two years in all cases, that can be done but poses an unnecessary complication of extracting the from/to years when you could just have them separately in the Afrapportering sheet.

There are also many ways to achieve the same thing; you don't actually need to duplicate the filtering fields in Afrapportering - you could instead filter by whichever columns you want to in Risikolog using the autofilter you've got in the header and export the filtered results to another sheet (e.g. Afrapportering), which is what I suggest. Doing it this way, you're also not restricted to filtering only by the date & severity fields.

I would normally use named ranges to reference columns under the headings you need reported as this avoids using explicit references to them but I'm not sure if you know what they are or how to set them up. The "offset" references in the code below are a relative reference from the first column, which means if you add/remove columns, the data returned will be wrong. If you know how to apply named ranges then let me know what you named them (or just send an updated file) and I'll change the code.

Code is below - you need to paste this into the code window of the Afrapportering sheet.

Please Login or Register  to view this content.