Incrementing All References in Continuing

[Winnie Ellerman]

Please help! I have been teaching myself excel to help out my school with a more efficient grade entering system. I am creating a workbook that has all the students marks entered in rows on the first sheet called Marks!, analysis of grades for the entire class on the second sheet called Analysis!, then subsequent sheets have each student's individualized report card called Report (1), Report (2), etc. The cells in the report card reference the Marks! Sheet in a lot of different places.

Is there any way to create a Report (2) sheet that increments each cell reference in Report (1) by one? I have to make 70 report cards and copying the formulas by hand leaves room for error and will take days.

When I looked at similar projects that people posted here, it seemed like there's a way to increment one cell but was lost trying to figure out how to do each reference, the same kind of way the autofill option works within on worksheet.

 

[Scott T]

You could lookup the grades by name then you could make a copy of the report sheet and change the name and all the grades will update.

​

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]name[/TD]
[TD]grade1[/TD]
[TD]grade2[/TD]
[TD]grade3[/TD]
[TD]grade4[/TD]
[TD]grade5[/TD]
[TD]grade6[/TD]
[TD]grade7[/TD]
[TD]grade8[/TD]
[TD]grade9[/TD]
[TD]grade10[/TD]
[TD]grade11[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Name1[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Name2[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Name3[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Name4[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Name5[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Name6[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Name7[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

Marks​

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Report card[/TD]
[TD]grade1[/TD]
[TD]grade2[/TD]
[TD]grade3[/TD]
[TD]grade4[/TD]
[TD]grade5[/TD]
[TD]grade6[/TD]
[TD]grade7[/TD]
[TD]grade8[/TD]
[TD]grade9[/TD]
[TD]grade10[/TD]
[TD]grade11[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Name1[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

Report (1)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=INDEX(Marks!$B$2:$L$8,MATCH($A$2,Marks!$A$2:$A$8,0),COLUMNS(($B2:B2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Copy the formula in B2 across


Then make a copy of Report (1) and change the name and all the grades will update.
[TABLE="class: grid, width: 500"]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Report card[/TD]
[TD]grade1[/TD]
[TD]grade2[/TD]
[TD]grade3[/TD]
[TD]grade4[/TD]
[TD]grade5[/TD]
[TD]grade6[/TD]
[TD]grade7[/TD]
[TD]grade8[/TD]
[TD]grade9[/TD]
[TD]grade10[/TD]
[TD]grade11[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Name2[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

Report (2)​

 

[tonyyy]

Winnie Ellerman,

Welcome to the Board.

I have been teaching myself excel...

Good for you!

You might consider exploring Excel's PivotTable feature, using the data on the Marks worksheet as the source. PivotTable reports are highly configurable and produce consistent results.

Cheers,

tonyyy

 

[Winnie Ellerman]

Hi Scott!

Thank you for your help and detailed comment. In my Marks sheet, I have subject names in each column and student names in each row, like you have shown. However, some students don't take all the class so the report card that I have created has a lot of if statements allowing the value to only show up if the student has marks for the class.

I'm not entirely sure how to implement your suggestion into my sheet and still keep in tact all the things that my formulas currently do. Any ideas?

Thanks again!

Winnie

 

[Scott T]

If the cell will be blank when a student does not take a class then you can use the formula below to return "blank" instead of a 0 to the cell on the report sheet

=IF(INDEX(Marks!$B$2:$L$8,MATCH($A$2,Marks!$A$2:$A$8,0),COLUMNS(($B2:B2)))=0,"",INDEX(Marks!$B$2:$L$8,MATCH($A$2,Marks!$A$2:$A$8,0),COLUMNS(($B2:B2))))