0

I'm deeply confused about using OFFSET with ROW to grab every nth row of a sheet

I have a marking sheet that looks like this:

enter image description here

And am trying to transfer those grades into a summary sheet where each row represents the scores for one student that are spread around the nicely formatted grading sheet

enter image description here

My summary sheet looks like this. In each cell I have entered the row number for each piece of information in the marking rubric.

how would I write the formula to reference these cells for the first column (Identification)? I have tried to use
OFFSET($B$5,ROW(A1)*n-1,0)
for this, but cannot figure out how to specify it to grab the right rows. e.g. 8, 44, 80, 116, etc.

Improve this question
3
  • I answered this myself. Thanks I have used: =OFFSET('Individual Sheets'!$G$1, (ROW(D1)-1)*36+8-1, 0) Commented Sep 25, 2024 at 14:48
  • 1
    Alan, you can post a formal answer and accept it if you've resolved it Commented Sep 25, 2024 at 20:40
  • @AlanNielsen For a non-volatile function, use INDEX instead of OFFSET. eg: =INDEX($B:$B,SEQUENCE(5,,8,36)) modified as you need. If you need to return the entire row, assuming the table is in columns B:D, then =INDEX($B:$D,SEQUENCE(5,,8,36), SEQUENCE(,3)). This is entered in a single cell and the results will SPILL. Also, you must have a version of Excel that includes the SEQUENCE function. If you don't, there is another way to generate that sequence. Commented Sep 26, 2024 at 0:46
Related questions
639
Stop Excel from automatically converting certain text values to dates
3
Microsoft Excel – How to copy cells to a different worksheet on every nth row
207
How to keep one variable constant with other one changing with row in excel
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.