I am trying to analyse student marks over a period of 3 years, to determine value added by the school. I have 3 spreadsheets, for each of the 3 years. Unfortunately, the LMS the school is using has exported the data in 5 rows per student, showing their mark for each of terms 1-4, plus their final year mark, with the subjects listed in the columns. I only want to extract the data in the 5th row for each student, and paste that into a new spreadsheet for each year. e.g. I have a Gr 10 schedule sheet, and want to create a Gr 10 final mark sheet just with the final marks for each student. I'm not pasting the data I want to extract into the same sheet. The first piece of datum I want to extract is in D9. Thereafter, I want the data from every 5th row (D14, D19, D24, D29, etc.). I'm pasting that into a new sheet, into C3. So in my new sheet, C4 will have 'schedule'!D19, and C5 will have 'schedule'!D24, etc. The image attached shows the top left corner of my schedule sheet, with the data I want to extract highlighted in red.
I am trying to adapt an OFFSET function I found elsewhere here, but when I drag it down to auto-fill, it just isn't working. Either I get the error message that there's a problem with my formula, or I get a #value error, or it works, but it's pulling in the wrong data.
To complicate matters, the data I want to extract from the columns is also every 2nd column, because the schedule lists the mark and then the code. I only want the marks. So whatever formula I use, when I drag across to autofill needs to skip every 2nd column. I realise I'm probably going to have to extract the data in two steps, so I'd prefer to drag down and auto-fill the rows for one subject, and then amend the formula to drag across to autofill the columns with the different subjects, as there are only 15 subjects (columns), and nearly 1000 students - it's easier to correct 15 formulae and then drag down than correct 1000 and drag across. Of course, if there's a way to do the whole lot in one fell swoop, that would be even better.
I am trying to adapt an OFFSET function I found elsewhere here, but when I drag it down to auto-fill, it just isn't working. Either I get the error message that there's a problem with my formula, or I get a #value error, or it works, but it's pulling in the wrong data.
To complicate matters, the data I want to extract from the columns is also every 2nd column, because the schedule lists the mark and then the code. I only want the marks. So whatever formula I use, when I drag across to autofill needs to skip every 2nd column. I realise I'm probably going to have to extract the data in two steps, so I'd prefer to drag down and auto-fill the rows for one subject, and then amend the formula to drag across to autofill the columns with the different subjects, as there are only 15 subjects (columns), and nearly 1000 students - it's easier to correct 15 formulae and then drag down than correct 1000 and drag across. Of course, if there's a way to do the whole lot in one fell swoop, that would be even better.
