OFFSET formula not working

nimming

New Member
Joined
Nov 5, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Attachments

  • student data extract.jpg
    student data extract.jpg
    100.8 KB · Views: 16

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
Solution

nimming

New Member
Joined
Nov 5, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Thanks. I found a workaround yesterday using different exports from the LMS, so problem solved for now!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,650
Messages
5,626,084
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top