Forum Discussion
Formula support
I am trying to develop feedback sheets for students at school and wanted to know if there is a quick way of ensuring the formulae I have used auto-adjust to the row that the student's data is on, rat...
lgriffalo To calculate the "student number", use the ROW() function divided by the total number of rows in the feedback form (including the blank row that separates each student's feedback form), then ROUNDUP the results to the nearest whole number:
=ROUNDUP(ROW()/ROWS(A1:A12), 0)
//OR
=ROUNDUP(ROW()/12, 0)
This formula can then be used as the row_num argument in the INDEX function to return the corresponding student name in cell A2:
=INDEX('Data entry'!$A$5:$A$1000, ROUNDUP(ROW()/12, 0))
The XLOOKUP function can then be used to return the entire row of data from column C to F on the 'Data entry' sheet where the student name in cell A2 is found in the list of names in column A of the 'Data entry' sheet:
=XLOOKUP(A2, 'Data entry'!$A$5:$A$1000, 'Data entry'!$C$5:$J$1000)
This formula can then be used as the [sum_range] argument in the SUMIF function to return the total marks achieved for each corresponding assessment or specification code. For example, the formula in cell C4 would be:
=SUMIF('Data entry'!$C$3:$J$3, TEXTBEFORE(A4,":"), XLOOKUP(A2, 'Data entry'!$A$5:$A$1000, 'Data entry'!$C$5:$J$1000))
Please see the attached workbook which contains the updated formulas. To create a feedback form for the next student, "Charles Darwin", select the entire range of the first feedback form (A1:E11), then copy and paste it in cell A25. Cheers!
lgriffalo Further to my previous reply, an alternative approach you might consider is to have only one feedback form with a data validation list in cell A2 to select the student and update the report (see attached workbook). This way, you wouldn't need to copy and paste the entire feedback form multiple times for every student in the list.