Relative Column Position

Question

REFERENCE EXCEL FILE:
https://www.dropbox.com/s/tltm16bvcolc6b3/Master%20EMT%20Class%20Template%20-%20Master%20Copy%20%281%29.xlsm?dl=0

WORKSHEETS:

1. Quiz Paper
2. Class Summary

Here's my question:

Is there a way to reference a column relative to its position within a range? If not, then how do I accomplish the below task?

If you look on the Class Summary worksheet, starting at row 71, I am trying to pull in all grades pertaining to the student's name. The quiz grades are located in the worksheet "Quiz Paper". I use an OFFSET function for the first grade, in CLASS SUMMARY, to help me see what I want to do. At this point, I am not looking up the grade based on the student's name, which is "A, Student" in this case. The offset for each grade in QUIZ PAPER is 18 rows within the same column.

I was hoping to come up with a formula for the "rows" criteria in the OFFSET Function so that as I copy the formula across, I would not have to change anything. Hence, the question I pose above.

I appreciate any help and if I need to go down a different path, I am open to that option also.

Les

Answer 1

Les,

I meant with get rid of the merged cells also use serried cells .

But if that is not possible then you can use formulas like this:

Formula in R80:

=OFFSET('Quiz Paper'!$E$15,(QUOTIENT(COLUMN(A1)-1,4)+1)*18,ROW(A1)-1)

Formula in R79:

R79: =TEXT(OFFSET('Quiz Paper'!$I$2,(QUOTIENT(COLUMN(A1)-1,4)+1)*18,ROW(A1)-1),"mm/dd;;")

and drag them down and/or to the right.

Jan

Answer 2

Even getting rid of the merged cells, it will still be 4 columns in width. I cannot change that and I also need to center the grade within the four columns.

Answer 3

Les,

Ah, yes I didn't see that.

The right thing to do (my opinion) is to get rid of all the combined cells to avoid this kind of problems.

Probably useful for lay-out but for calculations/formula's far from convenient.

Jan

Answer 4

This is on track for what I need but it needs to be tweaked and I don't know how to tweak it.

Below is the results

DATES -->	09/15	10/12	11/10	12/07	01/13	02/21
A, Student	40	70	80	20	80	100						0	0	0	0	0	0	0	0	0	0	0

The column with the grade of 100 is actually Quiz #21. The date and grade for Quiz #6 should be in that spot. The formula skips quizzes I believe the issue comes with the fact that each spot for the date and grade has a column width of 4.

Out of curiosity, is there a way retrieve the same data using some type of lookup using the student's name as a reference?

Les

Answer 5

Les,

Perhaps this might do:

Formula in R80:

=OFFSET('Quiz Paper'!$E$15,(COLUMN(A1)-1)*18,ROW(A1)-1)

Drag the formula down and to the right as far as needed

Formula in R79:

=TEXT(OFFSET('Quiz Paper'!$I$2,(COLUMN(A1)-1)*18,ROW(A1)-1),"mm/dd;;")

Drag to the right as far as needed.

The students has to be in the same order in Class Summary as in Quiz Paper.

If that is not the case then you should use a kind of lookup function for Student.

Jan